/*
Run this script on:

        SRV-PRODTK01.TK_GMW    -  This database will be modified

to synchronize it with:

        SRV-PRODTK01.TK_GMWTest

You are recommended to back up your database before running this script

Script created by SQL Compare version 9.0.0 from Red Gate Software Ltd at 01/03/2012 11.52.00

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping constraints from [dbo].[Odette]'
GO
ALTER TABLE [dbo].[Odette] DROP CONSTRAINT [PK_Odette]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping index [i_UDC] from [dbo].[Odette_storico]'
GO
DROP INDEX [i_UDC] ON [dbo].[Odette_storico]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[ElencoCartellini]'
GO
ALTER TABLE [dbo].[ElencoCartellini] ADD
[Note] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [RilPro].[AnagParticolari]'
GO
ALTER TABLE [RilPro].[AnagParticolari] ADD
[ClassifArticolo] [nchar] (1) COLLATE Latin1_General_CI_AS NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[v_selUdcByPart]'
GO
EXEC sp_refreshview N'[dbo].[v_selUdcByPart]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_rettMovAs400]'
GO

/***************************************
* STORED stp_rettMovAs400
*
* inserisce nella tabella per i trasferimenti batch verso magazzino AS una quantit di rettifica (con segno - )per UDC eventualmente gi scaricati a sistema
*
* Steamware, S.E.L.
* mod: 2010.11.02
*
****************************************/
ALTER PROCEDURE [dbo].[stp_rettMovAs400]
(
	@Particolare NVARCHAR(50),
	@UDC NVARCHAR(50),
	@CodMagAS NVARCHAR(50),
	@Qta DECIMAL(10,2),
	@CodEvento NVARCHAR(10)
)
AS

BEGIN
	
	INSERT INTO AS400_BatchMovimenti(DataIns, CodEvento, Particolare, UDC, CodMagAS, Quantita)
	VALUES (GETDATE(), @CodEvento, @Particolare, @UDC, @CodMagAS, -@Qta)

END

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[V_ParticolariOverview]'
GO
EXEC sp_refreshview N'[dbo].[V_ParticolariOverview]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_scaricaUdc]'
GO

-- Batch submitted through debugger: GMW_00401.sql|679|0|C:\Progetti\GMW\2011-12-14 vers test 401\Vers 401\GMW_00401.sql

/***************************************
* STORED stp_scaricaUdc
*
* elimina dalle posizioni correnti un UDC, salvandone la posizione precedente nella tab storica (per scaricamento / spedizione)
*
* Steamware, S.E.L.
* mod: 2010.09.28
*
****************************************/
ALTER PROCEDURE [dbo].[stp_scaricaUdc]
(
	@UDC VARCHAR(50),
	@IdxPosizione INT
)
AS

BEGIN TRAN
	-- libero la posizione attuale dell'UDC (se c'...)
	DELETE
	FROM	PosizioneUdcCorrente
	WHERE	UDC = @UDC
	
	-- aggiorno l'UDC come posizione finale...
	UPDATE	ElencoCartellini
	SET		IdxPosizione = @IdxPosizione, ModDate = GETDATE()
	WHERE	UDC = @UDC	
	
COMMIT TRAN

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_sbloccaUdc]'
GO

/***************************************
* STORED stp_sbloccaUdc
*
* sblocca un dato UDC per modifica
*
* Steamware, S.E.L.
* mod: 2010.04.28
*
****************************************/
ALTER PROCEDURE [dbo].[stp_sbloccaUdc]
(
	@UDC VARCHAR(50),
	@CodSoggetto NVARCHAR(17)
)
AS

DECLARE @CodEvento VARCHAR(10)
DECLARE @CodTipoDichiaraz VARCHAR(1)

SET @CodEvento = 'UDC_SBL'
SET @CodTipoDichiaraz = 'U'

BEGIN TRAN
	
	-- faccio update
	UPDATE      ElencoCartellini
	SET         ModDate = GETDATE(), CodSoggetto = @CodSoggetto
	WHERE		UDC = @UDC
	
COMMIT TRAN

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_UDC_updateQty]'
GO

/***************************************
* STORED stp_UDC_updateQty
*
* aggiorna un cartellino x la sua quantit
*
* Steamware, S.E.L.
* mod: 2010.06.12
*
****************************************/
ALTER PROCEDURE [dbo].[stp_UDC_updateQty]
(
	@UDC VARCHAR(50),
	@CodSoggetto VARCHAR(17),
	@CodTipoDichiaraz CHAR(1),
	@CodEvento VARCHAR(10),
	@Qta DECIMAL(10,2)
)
AS


------------------------------------------------------------------------------------------------------
-- Ciclo principale x edit record
------------------------------------------------------------------------------------------------------
BEGIN TRAN

	-- modifico il record 
	UPDATE	ElencoCartellini
	SET		Qta = @Qta, ModDate = GETDATE()
	WHERE	UDC = @UDC
	
COMMIT TRAN
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- restituisco la tab dati con l'udc appena inserito
------------------------------------------------------------------------------------------------------
SELECT * 
FROM ElencoCartellini
WHERE  UDC = @UDC
------------------------------------------------------------------------------------------------------

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[StoricoCartellini]'
GO
ALTER TABLE [dbo].[StoricoCartellini] ADD
[Note] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[v_specParticolari]'
GO
EXEC sp_refreshview N'[dbo].[v_specParticolari]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_rappQualAssociaUdc]'
GO

/***************************************
* STORED stp_rappQualAssociaUdc
*
* Associa l'UDC (creato) ad un rapp qualit di AS esistente SOLO SE UDC  NULL
*
* Steamware, S.E.L.
* mod: 2010.09.23
*
****************************************/
ALTER PROCEDURE [dbo].[stp_rappQualAssociaUdc]
(
	@ProgUDC NVARCHAR(10),
	@UDC NVARCHAR(50)
)
AS
	UPDATE	RilPro.RapQual
	SET		UDC = @UDC
	WHERE	(ProgUDC = @ProgUDC) AND (UDC IS NULL)
	
	-- restituisce la riga aggiornata
	SELECT * 
	FROM RilPro.RapQual 
	WHERE	ProgUDC = @ProgUDC
		
RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[v_AnagDatiParticolari]'
GO
EXEC sp_refreshview N'[dbo].[v_AnagDatiParticolari]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_annullaUDC]'
GO

/***************************************
* STORED stp_annullaUDC
*
* annulla un UDC
*
* Steamware, S.E.L.
* mod: 2010.04.28
*
****************************************/
ALTER PROCEDURE [dbo].[stp_annullaUDC]
(
	@UDC VARCHAR(50),
	@CodSoggetto VARCHAR(17)
)
AS

BEGIN TRAN
	
	-- faccio update
	UPDATE      ElencoCartellini
	SET         IdxPosizione = 0, ModDate = GETDATE()
	WHERE		UDC = @UDC
	
	
COMMIT TRAN

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[v_trasfDataMatrix]'
GO
EXEC sp_refreshview N'[dbo].[v_trasfDataMatrix]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_rappQualGetByUDC]'
GO

/***************************************
* STORED stp_rappQualGetByUDC
*
* Ottiene l'elenco dei record di rapp qualit di AS dato UDC
*
* Steamware, S.E.L.
* mod: 2011.03.30
*
****************************************/
ALTER PROCEDURE [dbo].[stp_rappQualGetByUDC]
(
	@UDC NVARCHAR(50)
)
AS
	-- restituisce le righe richieste
	SELECT	* 
	FROM	RilPro.RapQual 
	WHERE	(UDC = @UDC)
		
RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[v_righePrelevate]'
GO
EXEC sp_refreshview N'[dbo].[v_righePrelevate]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[stp_prtCartAnimeByUDC]'
GO

/***************************************
* STORED stp_prtCartAnimeByUDC
*
* ottiene il record del Cartellino ANIME dato l'UDC richiesto x la stampa
*
* Steamware, G.A.R.
* mod: 2012.02.27
*
****************************************/
CREATE PROCEDURE [dbo].[stp_prtCartAnimeByUDC]
(
	@UDC NVARCHAR(50)
)
AS

    -- estraggo i trattamenti nelle note ( x max 10 trattamenti )
    -- NON SERVE PER I CARTELLINI ANIME MA TENGO DATASET IDENTICO
	DECLARE @tmp		NVARCHAR(500)
	DECLARE @NoteTratt	NVARCHAR(500)
	SET @NoteTratt = ''

	-- SET @NoteTratt = (SELECT SUBSTRING(@tmp, 0, LEN(@tmp)))

	-- ora seleziono i dati veri e propri
	SELECT
		ElencoCartellini.UDC, ElencoCartellini.CodCS, ISNULL(ElencoCartellini.CodCliente, N'') AS codcliente, 
		ISNULL(ElencoCartellini.RagSociale, N'') AS ragsociale, ISNULL(ElencoCartellini.Particolare, N'') AS particolare,
		ISNULL(ElencoCartellini.DescParticolare, N'') AS DescParticolare, ISNULL(ElencoCartellini.DisegnoGrezzo, N'') AS DisegnoGrezzo, 
		ISNULL(ElencoCartellini.Esponente, N'') AS esponente, ISNULL(ElencoCartellini.CodImpianto, N'') AS CodImpianto,
		ISNULL(ElencoCartellini.DescImpianto, N'') AS DescImpianto, ISNULL(ElencoCartellini.CodStampo, N'') AS codstampo, 
		ISNULL(ElencoCartellini.Figura, N'') AS figura, ISNULL(ElencoCartellini.DataFus, N'') AS datafus,
		ISNULL(ElencoCartellini.TurnoFus, N'') AS turnofus, ISNULL(ElencoCartellini.CodImballo, N'') AS CodImballo, 
		ISNULL(ElencoCartellini.CodSoggetto, N'') AS CodSoggetto, ISNULL(ElencoCartellini.NumCont, N'') AS NumCont,
		ISNULL(ElencoCartellini.Tara, N'') AS tara, ISNULL(ElencoCartellini.Qta, N'') AS qta, ISNULL(ElencoCartellini.CodStato, N'') AS CodStato, 
		ISNULL(ElencoCartellini.IdxPosizione, N'') AS IdxPosizione,
		ISNULL(ElencoCartellini.PesoTot, N'') AS PesoTot, ISNULL(ElencoCartellini.PesoCad, N'') AS PesoCad, ElencoCartellini.CreateDate, 
		ElencoCartellini.ModDate, ISNULL(RilPro.AnagOperatori.Cognome, N'') AS cognome,
		ISNULL(RilPro.AnagOperatori.Nome, N'') AS nome, ISNULL(AnagStatiProdotto.DescStato, N'') AS DescStato, 
		ISNULL(RilPro.AnagImballi.DescImballo, N'') AS DescImballo, RilPro.AnagParticolari.CodFamiglia, RilPro.AnagFamiglie.DescFamiglia,
		ISNULL(ElencoCartellini.Note, N'') AS Note, @NoteTratt AS NoteTratt
	FROM
		RilPro.AnagParticolari 
	LEFT OUTER JOIN
		RilPro.AnagFamiglie 
	ON
		RilPro.AnagParticolari.CodFamiglia = RilPro.AnagFamiglie.CodFamiglia 
	RIGHT OUTER JOIN
		ElencoCartellini 
	ON
		RilPro.AnagParticolari.Particolare = ElencoCartellini.Particolare 
	LEFT OUTER JOIN
		AnagStatiProdotto 
	ON
		ElencoCartellini.CodStato = AnagStatiProdotto.CodStato 
	LEFT OUTER JOIN
		RilPro.AnagOperatori 
	ON
		ElencoCartellini.CodSoggetto = RilPro.AnagOperatori.CodSoggetto 
	LEFT OUTER JOIN
		RilPro.AnagImballi 
	ON
		ElencoCartellini.CodImballo = RilPro.AnagImballi.CodImballo 
	WHERE
		(ElencoCartellini.UDC = @UDC)
 

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[v_RapQualNote]'
GO

create VIEW [dbo].[v_RapQualNote]
AS
SELECT        rq.nRapQual, rq.DataRapQual, rq.ProgUDC, rq.CodFor, rq.DestTerz, rq.CodLega, rq.DataPrelFus, rq.TurnoPrelFus, rq.Qta, rq.DestLega, rq.LegaScaric, rq.BenesQual, 
                         rq.UDC, ISNULL(ec.Note, N'') AS Note
FROM            dbo.ElencoCartellini AS ec INNER JOIN
                         RilPro.RapQual AS rq ON ec.UDC = rq.UDC
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[stp_UDC_updateNoteByRQ]'
GO

/*****************************************
* STORED stp_UDC_updateNoteByRQ
*
* Aggiorna note UDC dato il rapp di qualit cui sono legate
*
* Steamware, S.E.L.
* mod: 2012.02.14
*
****************************************/
create PROCEDURE [dbo].[stp_UDC_updateNoteByRQ]
(
	@nRapQual NUMERIC(6,0),
	@note NVARCHAR(500)
)
AS

UPDATE	ElencoCartellini
SET		note = @note
FROM    ElencoCartellini AS ec INNER JOIN v_RapQualNote AS rq ON ec.UDC = rq.UDC
WHERE   (rq.nRapQual = @nRapQual)

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[stp_prtCartLiquidi_F18ByUDC]'
GO

/***************************************
* STORED stp_prtCartLiquidi_F18ByUDC
*
* ottiene il record del Cartellino Liquidi dato l'UDC richiesto x la stampa
*
* Steamware, S.E.L.
* mod: 2011.12.19
*
****************************************/
create PROCEDURE [dbo].[stp_prtCartLiquidi_F18ByUDC]
(
	@UDC NVARCHAR(50)
)
AS

    -- estraggo i trattamenti nelle note ( x max 10 trattamenti )
    -- NON SERVE PER I CARTELLINI F18
	DECLARE @tmp		NVARCHAR(500)
	DECLARE @NoteTratt	NVARCHAR(500)
	SET @tmp = ''
	SELECT  TOP 10 @tmp = @tmp + RIGHT(UDC_parent,8) + ' - ' from RelazUDC WHERE UDC_child = @UDC

	SET	@NoteTratt = (SELECT SUBSTRING(@tmp, 0, LEN(@tmp)))

	-- ora seleziono i dati veri e propri
	SELECT
		ElencoCartellini.UDC, ElencoCartellini.CodCS, ISNULL(ElencoCartellini.CodCliente, N'') AS codcliente, 
		ISNULL(ElencoCartellini.RagSociale, N'') AS ragsociale, ISNULL(ElencoCartellini.Particolare, N'') AS particolare,
		ISNULL(ElencoCartellini.DescParticolare, N'') AS DescParticolare, ISNULL(ElencoCartellini.DisegnoGrezzo, N'') AS DisegnoGrezzo, 
		ISNULL(ElencoCartellini.Esponente, N'') AS esponente, ISNULL(ElencoCartellini.CodImpianto, N'') AS CodImpianto,
		ISNULL(ElencoCartellini.DescImpianto, N'') AS DescImpianto, ISNULL(ElencoCartellini.CodStampo, N'') AS codstampo, 
		ISNULL(ElencoCartellini.Figura, N'') AS figura, ISNULL(ElencoCartellini.DataFus, N'') AS datafus,
		ISNULL(ElencoCartellini.TurnoFus, N'') AS turnofus, ISNULL(ElencoCartellini.CodImballo, N'') AS CodImballo, 
		ISNULL(ElencoCartellini.CodSoggetto, N'') AS CodSoggetto, ISNULL(ElencoCartellini.NumCont, N'') AS NumCont,
		ISNULL(ElencoCartellini.Tara, N'') AS tara, ISNULL(ElencoCartellini.Qta, N'') AS qta, ISNULL(ElencoCartellini.CodStato, N'') AS CodStato, 
		ISNULL(ElencoCartellini.IdxPosizione, N'') AS IdxPosizione,
		ISNULL(ElencoCartellini.PesoTot, N'') AS PesoTot, ISNULL(ElencoCartellini.PesoCad, N'') AS PesoCad, ElencoCartellini.CreateDate, 
		ElencoCartellini.ModDate, ISNULL(RilPro.AnagOperatori.Cognome, N'') AS cognome,
		ISNULL(RilPro.AnagOperatori.Nome, N'') AS nome, ISNULL(AnagStatiProdotto.DescStato, N'') AS DescStato, 
		ISNULL(RilPro.AnagImballi.DescImballo, N'') AS DescImballo, RilPro.AnagParticolari.CodFamiglia, RilPro.AnagFamiglie.DescFamiglia,
		ISNULL(ElencoCartellini.Note, N'') AS Note, @NoteTratt AS NoteTratt
	FROM
		RilPro.AnagParticolari 
	LEFT OUTER JOIN
		RilPro.AnagFamiglie 
	ON
		RilPro.AnagParticolari.CodFamiglia = RilPro.AnagFamiglie.CodFamiglia 
	RIGHT OUTER JOIN
		ElencoCartellini 
	ON
		RilPro.AnagParticolari.Particolare = ElencoCartellini.Particolare 
	LEFT OUTER JOIN
		AnagStatiProdotto 
	ON
		ElencoCartellini.CodStato = AnagStatiProdotto.CodStato 
	LEFT OUTER JOIN
		RilPro.AnagOperatori 
	ON
		ElencoCartellini.CodSoggetto = RilPro.AnagOperatori.CodSoggetto 
	LEFT OUTER JOIN
		RilPro.AnagImballi 
	ON
		ElencoCartellini.CodImballo = RilPro.AnagImballi.CodImballo 
	WHERE
		(ElencoCartellini.UDC = @UDC)
 

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[stp_prtCartLiquidi_F10ByUDC]'
GO

/***************************************
* STORED stp_prtCartLiquidi_F10ByUDC
*
* ottiene il record del Cartellino Liquidi dato l'UDC richiesto x la stampa
*
* Steamware, S.E.L.
* mod: 2011.12.19
*
****************************************/
create PROCEDURE [dbo].[stp_prtCartLiquidi_F10ByUDC]
(
	@UDC NVARCHAR(50)
)
AS

	-- estraggo i trattamenti nelle note ( x max 10 trattamenti )
	DECLARE @tmp		NVARCHAR(500)
	DECLARE @NoteTratt	NVARCHAR(500)
	SET @tmp = ''
	SELECT  TOP 10 @tmp = @tmp + RIGHT(UDC_parent,8) + ' - ' from RelazUDC WHERE UDC_child = @UDC

	SET	@NoteTratt = (SELECT SUBSTRING(@tmp, 0, LEN(@tmp)))

	-- ora seleziono i dati veri e propri
	SELECT
		ISNULL(ElencoCartellini.UDC, N'0000') AS UDC, ISNULL(ElencoCartellini.CodCS, N'') AS CodCS, ISNULL(ElencoCartellini.CodCliente, N'') AS codcliente, 
		ISNULL(ElencoCartellini.RagSociale, N'') AS ragsociale, ISNULL(ElencoCartellini.Particolare, N'') AS particolare,
		ISNULL(ElencoCartellini.DescParticolare, N'') AS DescParticolare, ISNULL(ElencoCartellini.DisegnoGrezzo, N'') AS DisegnoGrezzo, 
		ISNULL(ElencoCartellini.Esponente, N'') AS esponente, ISNULL(ElencoCartellini.CodImpianto, N'') AS CodImpianto,
		ISNULL(ElencoCartellini.DescImpianto, N'') AS DescImpianto, ISNULL(ElencoCartellini.CodStampo, N'') AS codstampo, 
		ISNULL(ElencoCartellini.Figura, N'') AS figura, ISNULL(ElencoCartellini.DataFus, N'') AS datafus,
		ISNULL(ElencoCartellini.TurnoFus, N'') AS turnofus, ISNULL(ElencoCartellini.CodImballo, N'') AS CodImballo, 
		ISNULL(ElencoCartellini.CodSoggetto, N'') AS CodSoggetto, ISNULL(ElencoCartellini.NumCont, N'') AS NumCont,
		ISNULL(ElencoCartellini.Tara, N'') AS tara, ISNULL(ElencoCartellini.Qta, N'') AS qta, ISNULL(ElencoCartellini.CodStato, N'') AS CodStato, 
		ISNULL(ElencoCartellini.IdxPosizione, N'') AS IdxPosizione,
		ISNULL(ElencoCartellini.PesoTot, N'') AS PesoTot, ISNULL(ElencoCartellini.PesoCad, N'') AS PesoCad, ElencoCartellini.CreateDate, 
		ElencoCartellini.ModDate, ISNULL(RilPro.AnagOperatori.Cognome, N'') AS cognome,
		ISNULL(RilPro.AnagOperatori.Nome, N'') AS nome, ISNULL(AnagStatiProdotto.DescStato, N'') AS DescStato, 
		ISNULL(RilPro.AnagImballi.DescImballo, N'') AS DescImballo, RilPro.AnagParticolari.CodFamiglia, RilPro.AnagFamiglie.DescFamiglia,
		ISNULL(ElencoCartellini.Note, N'') AS Note, ISNULL(@NoteTratt, N'') AS NoteTratt
	FROM
		RilPro.AnagParticolari 
	LEFT OUTER JOIN
		RilPro.AnagFamiglie 
	ON
		RilPro.AnagParticolari.CodFamiglia = RilPro.AnagFamiglie.CodFamiglia 
	RIGHT OUTER JOIN
		ElencoCartellini 
	ON
		RilPro.AnagParticolari.Particolare = ElencoCartellini.Particolare 
	LEFT OUTER JOIN
		AnagStatiProdotto 
	ON
		ElencoCartellini.CodStato = AnagStatiProdotto.CodStato 
	LEFT OUTER JOIN
		RilPro.AnagOperatori 
	ON
		ElencoCartellini.CodSoggetto = RilPro.AnagOperatori.CodSoggetto 
	LEFT OUTER JOIN
		RilPro.AnagImballi 
	ON
		ElencoCartellini.CodImballo = RilPro.AnagImballi.CodImballo 
	WHERE
		(ElencoCartellini.UDC = @UDC)
 

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[stp_UDC_delete_byRdQ_NEW]'
GO

/*****************************************
* STORED stp_UDC_delete_byRdQ
*
* Elimina gli UDC associati ad un rapporto di qualit dato rapporto qualit
*
* Steamware, S.E.L.
* mod: 2012.02.14
*
****************************************/
create PROCEDURE [dbo].[stp_UDC_delete_byRdQ_NEW]
(
	@nRapQual NUMERIC(6,0),
	@ExecBatchInter BIT  = 0 -- 0 Eseguita in Batch , 1 in Interattivo
)
AS

-- NON USATA **** 
-- NUOVA VERSIONE X VERIFICARE NEL CASO SIA ESEGUITA IN UNA TRANSAZIONE PRECEDENTE --- NON USATA

-- Attenzione!!! Cancella tutti i cartellini degli UDC materia prima del rapporto qualit anche se sono scaricati 
-- comunque sono presenti nello StoricoCartellini ( perdo per a che RapportoQualit facevano riferimento, mettere riferimento nelle note? )

/*---------------------------------------
- Query x eliminazione UDC e tab 
- collegate dato un Rapp di Qualit
---------------------------------------*/

/*  NON FUNZIONA A CAUSA DEI TRIGGER
WITH cte AS  -- Tutti i cartellini su AS400 annullati che hanno un cartellino in RilPro
(
    SELECT r.UDC
    FROM RilPro.RapQual AS r
    INNER JOIN dbo.ElencoCartellini AS e
    ON r.UDC = e.UDC
    WHERE r.BenesQual = 'A'
)
	-- Cancello la posizione degli UDC a magazzino
	DELETE dbo.PosizioneUdcCorrente
	FROM dbo.PosizioneUdcCorrente AS P
		INNER JOIN RilPro.RapQual AS r
		ON p.UDC = r.UDC
	WHERE r.nRapQual = @nRapQual
	-- SELECT * FROM dbo.PosizioneUdcCorrente WHERE r.nRapQual = @nRapQual

    -- Se non sono ancora scaricati cancello il record movimento di scarico materia prima
	DELETE dbo.AS400_BatchConsumoMP
	FROM dbo.AS400_BatchConsumoMP AS b
		INNER JOIN RilPro.RapQual AS r
		ON b.UDC = r.UDC
	WHERE r.nRapQual = @nRapQual AND b.DataInvioAs IS NULL
    
    SELECT * FROM dbo.AS400_BatchConsumoMP
    	
    -- Cancellazione Cartellini Materia Prima di un Rapporto qualit
    SELECT * FROM dbo.ElencoCartellini
*/

SET NOCOUNT ON;
SET ANSI_NULLS OFF;
SET XACT_ABORT ON;       -- Se c' un errore fa il rollback di tutta la transazione e non solo quella in errore
SET LOCK_TIMEOUT 600000; -- 10 min - Setto il tempo massimo di attesa del lock della tabella che  valido solo per la durata 
                         -- della connessione e di default  settato a -1 (infinito). ATTENZIONE che se viene dato un errore
                         -- di lock deve essere controllato 

IF @ExecBatchInter = 1     -- Se interattiva diminuisco il tempo di lock massimo a 15 sec
   SET LOCK_TIMEOUT 15000;

  DECLARE @TRAN AS INT, @State AS INT ;
  SET @TRAN =  @@TRANCOUNT;  SET @State = XACT_STATE();

-- Se per il rapporto qualit esiste almeno un cartellino UDC
IF ( SELECT COUNT(UDC) FROM RilPro.RapQual WHERE nRapQual = @nRapQual ) > 0
BEGIN
 BEGIN TRY;
  BEGIN TRANSACTION;
      
      -- =====================================================
      -- Cancella tutti gli UDC di un RQ 
      -- =====================================================
      SELECT ROW_NUMBER() OVER (ORDER BY UDC) AS Riga,UDC
      INTO #Temp
      FROM RilPro.RapQual
      WHERE nRapQual = @nRapQual;                -- Rapporto di qualit da cancellare

      DECLARE @Riga AS INT = 1
             ,@Fine AS INT = 0
             ,@UDC nVarchar(50);
      
      SELECT @Fine = MAX(Riga) FROM #Temp;

      WHILE @Riga <= @Fine
       BEGIN 
            -- Leggo UDC da cancellare uno alla volta
            SELECT @UDC = UDC FROM #Temp WHERE Riga = @Riga;

            -- ======================================================
            -- Cancella i dati del singolo UDC 
            -- =======================================================
            -- Attenzione che sulle cartelle ci sono dei trigger
            DELETE dbo.PosizioneUdcCorrente WHERE UDC = @UDC

            DELETE dbo.RelazUDC             WHERE UDC_child   = @UDC
            DELETE dbo.RelazUDC             WHERE UDC_parent  = @UDC
            DELETE dbo.ElencoCartellini     WHERE UDC = @UDC
          
            -- sgancio il cartellino AS400 da quello GMW ( cancellato )
            UPDATE RilPro.RapQual SET UDC = NULL WHERE UDC = @UDC

            --SELECT * FROM  dbo.PosizioneUdcCorrente WHERE UDC = @UDC
            --SELECT * FROM dbo.RelazUDC WHERE UDC_child = @UDC
            --SELECT * FROM dbo.ElencoCartellini WHERE UDC = @UDC
            --SELECT * FROM RilPro.RapQual WHERE UDC = @UDC

--       IF @nRapQual = 20 Select 1/0
       -- Select 1/0

        SET @Riga = @Riga + 1   -- Incremento contatore x UDC in tabella Temp
        -- SELECT @UDC
       END

       -- Se esiste la tabella #Temp la cancello
       IF OBJECT_ID(N'tempdb..#Temp') IS NOT NULL
         DROP TABLE #Temp;

   COMMIT TRANSACTION;
  END TRY
 
  BEGIN CATCH;

      --IF (ERROR_NUMBER() = 1222)             -- se vi  un lock per troppo tempo posso controllarlo x rieseguire
      --     BEGIN; PRINT 'Tabella Bloccata';  -- Segnalo l'errore specifico di lock
      --     END;
      --ELSE PRINT 'Errore Generico';          -- se l'errore non  un deadlock esco dal WHILE
  
      IF XACT_STATE() <> 0 AND @TRAN = 0  -- solo se vi sono transazioni aperte faccio il rollback ( da verificare nel caso sia richiamato 
          ROLLBACK TRANSACTION            -- all'interno di un'altra transazione )

      -- Cancello La Tabella TEMP se esiste ( messo x per sicurezza )
      -- EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='#Temp')
      -- *** QUI HO IL PROBLEMA SE XACT_STATE() = -1 perch da errore 
      IF OBJECT_ID(N'tempdb..#Temp') IS NOT NULL
          DROP TABLE #Temp;
      
      -- Uso RAISERROR mandare in errore la procedura e x per tornare le info 
      -- sull'errore originale che ha portato l'esecuzione nel blocco CATCH
      DECLARE @ErrorNumber INT;
      DECLARE @ErrorMessage NVARCHAR(4000);
      DECLARE @ErrorSeverity INT;
      DECLARE @ErrorState INT;
      SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(), @ErrorNumber = ERROR_NUMBER();

      IF @ExecBatchInter = 0  -- Sollevo errore solo se richiesto dai parametri ( es. esecuzione da un Job Batch )
        RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  -- Se non uso RAISERROR la procedura non mi da errore es. se schedulata
 
  END CATCH;

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_prtCartByUDC]'
GO

/***************************************
* STORED stp_prtCartByUDC
*
* ottiene il record del Cartellino dato l'UDC richiesto x la stampa
*
* Steamware, S.E.L.
* mod: 2011.12.19
*
****************************************/
ALTER PROCEDURE [dbo].[stp_prtCartByUDC]
(
	@UDC VARCHAR(50)
)
AS
	SELECT
		ElencoCartellini.UDC, ElencoCartellini.CodCS, ISNULL(ElencoCartellini.CodCliente, N'') AS codcliente, 
		ISNULL(ElencoCartellini.RagSociale, N'') AS ragsociale, ISNULL(ElencoCartellini.Particolare, N'') AS particolare,
		ISNULL(ElencoCartellini.DescParticolare, N'') AS DescParticolare, ISNULL(ElencoCartellini.DisegnoGrezzo, N'') AS DisegnoGrezzo, 
		ISNULL(ElencoCartellini.Esponente, N'') AS esponente, ISNULL(ElencoCartellini.CodImpianto, N'') AS CodImpianto,
		ISNULL(ElencoCartellini.DescImpianto, N'') AS DescImpianto, ISNULL(ElencoCartellini.CodStampo, N'') AS codstampo, 
		ISNULL(ElencoCartellini.Figura, N'') AS figura, ISNULL(ElencoCartellini.DataFus, N'') AS datafus,
		ISNULL(ElencoCartellini.TurnoFus, N'') AS turnofus, ISNULL(ElencoCartellini.CodImballo, N'') AS CodImballo, 
		ISNULL(ElencoCartellini.CodSoggetto, N'') AS CodSoggetto, ISNULL(ElencoCartellini.NumCont, N'') AS NumCont,
		ISNULL(ElencoCartellini.Tara, N'') AS tara, ISNULL(ElencoCartellini.Qta, N'') AS qta, ISNULL(ElencoCartellini.CodStato, N'') AS CodStato, 
		ISNULL(ElencoCartellini.IdxPosizione, N'') AS IdxPosizione,
		ISNULL(ElencoCartellini.PesoTot, N'') AS PesoTot, ISNULL(ElencoCartellini.PesoCad, N'') AS PesoCad, ElencoCartellini.CreateDate, 
		ElencoCartellini.ModDate, ISNULL(RilPro.AnagOperatori.Cognome, N'') AS cognome,
		ISNULL(RilPro.AnagOperatori.Nome, N'') AS nome, ISNULL(AnagStatiProdotto.DescStato, N'') AS DescStato, 
		ISNULL(RilPro.AnagImballi.DescImballo, N'') AS DescImballo, RilPro.AnagParticolari.CodFamiglia, RilPro.AnagFamiglie.DescFamiglia,
		ISNULL(ElencoCartellini.Note, N'') AS Note 
	FROM
		RilPro.AnagParticolari 
	LEFT OUTER JOIN
		RilPro.AnagFamiglie 
	ON
		RilPro.AnagParticolari.CodFamiglia = RilPro.AnagFamiglie.CodFamiglia 
	RIGHT OUTER JOIN
		ElencoCartellini 
	ON
		RilPro.AnagParticolari.Particolare = ElencoCartellini.Particolare 
	LEFT OUTER JOIN
		AnagStatiProdotto 
	ON
		ElencoCartellini.CodStato = AnagStatiProdotto.CodStato 
	LEFT OUTER JOIN
		RilPro.AnagOperatori 
	ON
		ElencoCartellini.CodSoggetto = RilPro.AnagOperatori.CodSoggetto 
	LEFT OUTER JOIN
		RilPro.AnagImballi 
	ON
		ElencoCartellini.CodImballo = RilPro.AnagImballi.CodImballo 
	WHERE
		(ElencoCartellini.UDC = @UDC)
 

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[ElencoPostazioni]'
GO
CREATE TABLE [dbo].[ElencoPostazioni]
(
[codPostazione] [nvarchar] (250) COLLATE Latin1_General_CI_AS NOT NULL,
[stampante] [nvarchar] (250) COLLATE Latin1_General_CI_AS NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_ElencoPostazioni] on [dbo].[ElencoPostazioni]'
GO
ALTER TABLE [dbo].[ElencoPostazioni] ADD CONSTRAINT [PK_ElencoPostazioni] PRIMARY KEY CLUSTERED  ([codPostazione])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[stp_elencoPostaziont_getByCod]'
GO

/***************************************
* STORED stp_elencoPostaziont_getByCod
*
* ottiene il una postazione dato il codice
*
* Steamware, S.E.L.
* mod: 2010.11.17
*
****************************************/
create PROCEDURE [dbo].[stp_elencoPostaziont_getByCod]
(
	@codPostazione NVARCHAR(250)
)
AS

SELECT      *
FROM        ElencoPostazioni
WHERE       codPostazione = @codPostazione

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_UDC_updateIdxPosizione]'
GO

/***************************************
* STORED stp_UDC_updateIdxPosizione
*
* aggiorna un cartellino x la sua posizione logica
*
* Steamware, S.E.L.
* mod: 2010.06.12
*
****************************************/
ALTER PROCEDURE [dbo].[stp_UDC_updateIdxPosizione]
(
	@UDC VARCHAR(50),
	@IdxPosizione INT
)
AS


------------------------------------------------------------------------------------------------------
-- Ciclo principale x edit record
------------------------------------------------------------------------------------------------------
BEGIN TRAN	
	
	-- modifico il record 
	UPDATE	ElencoCartellini
	SET		IdxPosizione = @IdxPosizione, ModDate = GETDATE()
	WHERE	UDC = @UDC
	
COMMIT TRAN
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- restituisco la tab dati con l'udc appena inserito
------------------------------------------------------------------------------------------------------
SELECT * 
FROM ElencoCartellini
WHERE  UDC = @UDC
------------------------------------------------------------------------------------------------------

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[stp_RQN_getByRQ]'
GO

/*****************************************
* STORED stp_RQN_getByRQ
*
* Recupera dati Rapporto Qualit + note da cod rapporto qualit
*
* Steamware, S.E.L.
* mod: 2012.02.14
*
****************************************/
create PROCEDURE [dbo].[stp_RQN_getByRQ]
(
	@nRapQual NUMERIC(6,0)
)
AS

SELECT	nRapQual, DataRapQual, ProgUDC, CodFor, DestTerz, CodLega, DataPrelFus, TurnoPrelFus, Qta, DestLega, LegaScaric, BenesQual, UDC, Note
FROM    v_RapQualNote
WHERE   (nRapQual = @nRapQual)

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[stp_UDC_associaParent]'
GO

-- Batch submitted through debugger: GMW_00401.sql|723|0|C:\Progetti\GMW\2011-12-14 vers test 401\Vers 401\GMW_00401.sql

/***************************************
* STORED stp_UDC_associaParent
*
* crea un nuovo record nella di associazione UDC/parent
*
* Steamware, S.E.L.
* mod: 2011.12.06
*
****************************************/
create PROCEDURE [dbo].[stp_UDC_associaParent]
(
	@UDC NVARCHAR(50),
	@UDC_parent NVARCHAR(50)
)
AS

	-- elimino partent vuoti...
	DELETE 
	FROM RelazUDC
	WHERE ISNULL(UDC_parent,'') = ''
	-- inserisco relazione parent-child tra UDC vecchio (tara) e nuovo (pesa)
	INSERT INTO RelazUDC(UDC_parent, UDC_child)
	VALUES (@UDC_parent, @UDC)

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[stp_UDC_getLast_ByParticolare]'
GO

/***************************************
* STORED stp_UDC_getLast_ByParticolare
*
* elenco cartellini ULTIMO creati x particolare
*
* Steamware, S.E.L.
* mod: 2011.11.14
*
****************************************/
create PROCEDURE [dbo].[stp_UDC_getLast_ByParticolare]
(
	@CodCS VARCHAR(2),
	@Particolare VARCHAR(15)
)
AS

	-- prendo ultimo UDC creato x il particolare
	SELECT		TOP 1 * FROM ElencoCartellini 
	WHERE		(Particolare= @Particolare) AND CodCS = @CodCS
	ORDER BY	CreateDate DESC

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[v_particolariEsponenteFigura]'
GO
EXEC sp_refreshview N'[dbo].[v_particolariEsponenteFigura]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [RilPro].[vDatiLineeGiornata]'
GO
EXEC sp_refreshview N'[RilPro].[vDatiLineeGiornata]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[v_UdcDetail]'
GO

ALTER VIEW [dbo].[v_UdcDetail]
AS
SELECT        dbo.ElencoCartellini.UDC, ISNULL(dbo.ElencoCartellini.RagSociale, N'') AS RagSociale, ISNULL(dbo.ElencoCartellini.Particolare, N'') AS Particolare, 
                         ISNULL(dbo.ElencoCartellini.DescParticolare, N'') AS DescParticolare, ISNULL(dbo.ElencoCartellini.DisegnoGrezzo, N'') AS DisegnoGrezzo, 
                         ISNULL(dbo.ElencoCartellini.Esponente, N'') AS Esponente, ISNULL(dbo.ElencoCartellini.DescImpianto, N'') AS DescImpianto, ISNULL(dbo.ElencoCartellini.DataFus, 
                         N'') AS DataFus, ISNULL(dbo.ElencoCartellini.TurnoFus, 0) AS TurnoFus, dbo.ElencoCartellini.CodImballo, dbo.ElencoCartellini.Qta, 
                         ISNULL(dbo.AnagStatiProdotto.DescStato, N'') AS DescStato, dbo.ElencoCartellini.ModDate, ISNULL(dbo.Blocchi.CodMag, N'') AS CodMag, 
                         ISNULL(dbo.Blocchi.CodBlocco, N'') AS CodBlocco, ISNULL(dbo.Celle.CodCella, N'') AS CodCella, ISNULL(dbo.Celle.IdxCella, 0) AS IdxCella, ISNULL(dbo.Celle.X, 0) 
                         AS X, ISNULL(dbo.Celle.Y, 0) AS Y, ISNULL(dbo.Celle.Z, 0) AS Z, dbo.ElencoCartellini.IdxPosizione, dbo.ElencoCartellini.Note
FROM            dbo.AnagStatiProdotto RIGHT OUTER JOIN
                         dbo.ElencoCartellini ON dbo.AnagStatiProdotto.CodStato = dbo.ElencoCartellini.CodStato AND 
                         dbo.AnagStatiProdotto.CodStato = dbo.ElencoCartellini.CodStato LEFT OUTER JOIN
                         dbo.Celle INNER JOIN
                         dbo.PosizioneUdcCorrente ON dbo.Celle.IdxCella = dbo.PosizioneUdcCorrente.IdxCella AND dbo.Celle.IdxCella = dbo.PosizioneUdcCorrente.IdxCella AND 
                         dbo.Celle.IdxCella = dbo.PosizioneUdcCorrente.IdxCella INNER JOIN
                         dbo.Blocchi ON dbo.Celle.IdxBlocco = dbo.Blocchi.IdxBlocco INNER JOIN
                         dbo.AnagMag ON dbo.Blocchi.CodMag = dbo.AnagMag.CodMag AND dbo.Blocchi.CodCS = dbo.AnagMag.CodCS AND 
                         dbo.Blocchi.CodMag = dbo.AnagMag.CodMag AND dbo.Blocchi.CodCS = dbo.AnagMag.CodCS AND dbo.Blocchi.CodMag = dbo.AnagMag.CodMag AND 
                         dbo.Blocchi.CodCS = dbo.AnagMag.CodCS ON dbo.ElencoCartellini.UDC = dbo.PosizioneUdcCorrente.UDC
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[V_statoCelleCapienzaAssegnati]'
GO
EXEC sp_refreshview N'[dbo].[V_statoCelleCapienzaAssegnati]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[V_MagazziniOverview]'
GO
EXEC sp_refreshview N'[dbo].[V_MagazziniOverview]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[V_DettMagPart]'
GO
EXEC sp_refreshview N'[dbo].[V_DettMagPart]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[V_CelleOverview]'
GO
EXEC sp_refreshview N'[dbo].[V_CelleOverview]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_UDC_update]'
GO

/***************************************
* STORED stp_UDC_update
*
* aggiorna un cartellino dato il suo codice UDC e tutti i parametri da aggiornare
*
* Steamware, S.E.L.
* mod: 2010.04.30
*
****************************************/
ALTER PROCEDURE [dbo].[stp_UDC_update]
(
	@UDC_edit VARCHAR(50),
	@CodCS VARCHAR(2),
	@Flusso VARCHAR(2),
	@Anno VARCHAR(2),
	@CodCliente VARCHAR(6),
	@Particolare VARCHAR(15),
	@CodImpianto AS VARCHAR(50),
	@CodStampo VARCHAR(8),
	@Esponente VARCHAR(6),
	@Figura VARCHAR(4),
	@DataFus DATETIME,
	@TurnoFus INT,
	@CodImballo VARCHAR(15),
	@CodSoggetto VARCHAR(17),	
	@Tara FLOAT,
	@IdxPosizione INT,
	@CodTipoDichiaraz CHAR(1),
	@CodEvento VARCHAR(10),
	@Qta DECIMAL(10,2),
	@PesoTot FLOAT,
	@PesoCad FLOAT,
	@CodStato VARCHAR(50),
	@UDC_parent VARCHAR(50),
	@Note NVARCHAR(500)
)
AS

-- DECLARE iniziali
DECLARE @RagSociale AS VARCHAR(35)
DECLARE @DescParticolare AS VARCHAR(30)
DECLARE @DescImpianto AS VARCHAR(50)
DECLARE @DisegnoGrezzo AS VARCHAR(30)
DECLARE @NumCont AS INT
DECLARE @numFlu AS INT
DECLARE @numUdc AS INT
DECLARE @nextUdc AS INT

------------------------------------------------------------------------------------------------------
-- Caricamento Dati da anagrafica
------------------------------------------------------------------------------------------------------
-- cerco la ragione sociale...
SET @RagSociale = (
	SELECT        ISNULL(RagSociale, 'ND') AS RagSociale
	FROM            RilPro.AnagClienti
	WHERE        (CodCliente = @CodCliente)
)

-- Cerco descrizione del particolare...
SET @DescParticolare = (
	SELECT        ISNULL(DescParticolare, 'ND') AS DescParticolare
	FROM            RilPro.AnagParticolari
	WHERE        (Particolare = @Particolare)
)

-- cerco codice disegno grezzo
SET @DisegnoGrezzo = (
	SELECT        ISNULL(DisegnoGrezzo, 'ND') AS DescParticolare
	FROM            RilPro.AnagParticolari
	WHERE        (Particolare = @Particolare)
)

-- cerco descrizione Impianto
SET @DescImpianto = (
	SELECT        ISNULL(DescImpianto, 'ND') AS DescImpianto
	FROM            AnagImpianti
	WHERE        (CodImpianto = @CodImpianto)
)



-- Calcolo contatore intero successivo x il contenitore...
SET @NumCont = (
	SELECT      NumCont
	FROM        ElencoCartellini
	WHERE       (UDC = @UDC_edit)	
)


------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- Flusso
------------------------------------------------------------------------------------------------------
-- controllo se esiste il flusso (bilancia)...
SET @numFlu = (
	SELECT count(*)
	FROM AnagBilance
	WHERE	CodBilancia	= @Flusso
)
-- ...senn lo creo...
IF(@numFlu = 0)
	BEGIN
		-- se c' inserisco in tab
		INSERT INTO AnagBilance(CodBilancia, DescrImpianto, CodCS)
		VALUES (@Flusso, @Flusso, @CodCS)
	END
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- Ciclo principale x edit record
------------------------------------------------------------------------------------------------------
BEGIN TRAN
	
	-- elimino vecchio record parent/child
	DELETE FROM RelazUDC
	WHERE (UDC_child = @UDC_edit)
	
	-- modifico il record 
	UPDATE	ElencoCartellini
	SET		CodCS = @CodCS, CodCliente = @CodCliente, RagSociale = @RagSociale, Particolare = @Particolare, DescParticolare = @DescParticolare,
			DisegnoGrezzo = @DisegnoGrezzo, Esponente = @Esponente, CodImpianto = @CodImpianto, DescImpianto = @DescImpianto, CodStampo = @CodStampo,
			Figura = @Figura, DataFus = @DataFus, TurnoFus = @TurnoFus, CodImballo = @CodImballo, CodSoggetto = @CodSoggetto, NumCont = @NumCont,
			Tara = @Tara, Qta = @Qta, CodStato = @CodStato, IdxPosizione = @IdxPosizione, PesoTot = @PesoTot, PesoCad = @PesoCad, ModDate = GETDATE(), Note = @Note 
	WHERE	UDC = @UDC_edit

	-- inserisco relazione parent-child tra UDC vecchio (tara) e nuovo (pesa)
	INSERT INTO RelazUDC(UDC_parent, UDC_child)
	VALUES (@UDC_parent, @UDC_edit)
COMMIT TRAN
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- restituisco la tab dati con l'udc appena inserito
------------------------------------------------------------------------------------------------------
SELECT * 
FROM ElencoCartellini
WHERE  UDC = @UDC_edit
------------------------------------------------------------------------------------------------------

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_getUdcDetailLikeSearch]'
GO

/***************************************
* STORED stp_getUdcDetailFullCode
*
* ottiene l'elenco degli UDC con i dati significativi partendo da un valore da cercare come LIKE in 
*  - UDC
*  - Particolare
*
* Steamware, S.E.L.
* mod: 2010.05.24
*
****************************************/
ALTER PROCEDURE [dbo].[stp_getUdcDetailLikeSearch]
(
	@searchVal VARCHAR(50),
	@CodCS VARCHAR(2)	
)
AS

SELECT		ElencoCartellini.UDC, ISNULL(ElencoCartellini.RagSociale, N'') AS RagSociale, ISNULL(ElencoCartellini.Particolare, N'') AS Particolare,
			ISNULL(ElencoCartellini.DescParticolare, N'') AS DescParticolare, ISNULL(ElencoCartellini.DisegnoGrezzo, N'') AS DisegnoGrezzo, 
            ISNULL(ElencoCartellini.Esponente, N'') AS Esponente, ISNULL(ElencoCartellini.DescImpianto, N'') AS DescImpianto,
            ISNULL(ElencoCartellini.DataFus, N'') AS DataFus, ISNULL(ElencoCartellini.TurnoFus, 0) AS TurnoFus, ElencoCartellini.CodImballo, 
            ElencoCartellini.Qta,  ISNULL(AnagStatiProdotto.DescStato, N'') AS DescStato, ElencoCartellini.ModDate, 
            ISNULL(Blocchi.CodMag, N'') AS CodMag, ISNULL(Blocchi.CodBlocco, N'') AS CodBlocco, ISNULL(Celle.CodCella, N'') AS CodCella, 
			ISNULL(dbo.Celle.IdxCella, 0) AS IdxCella, ISNULL(Celle.X, 0) AS X, ISNULL(Celle.Y, 0) AS Y, ISNULL(Celle.Z, 0) AS Z, ElencoCartellini.IdxPosizione,
			ISNULL(ElencoCartellini.Note, '') AS Note
FROM		AnagStatiProdotto RIGHT OUTER JOIN ElencoCartellini
			ON AnagStatiProdotto.CodStato = ElencoCartellini.CodStato AND AnagStatiProdotto.CodStato = ElencoCartellini.CodStato 
			LEFT OUTER JOIN Celle INNER JOIN PosizioneUdcCorrente 
			ON Celle.IdxCella = PosizioneUdcCorrente.IdxCella AND Celle.IdxCella = PosizioneUdcCorrente.IdxCella AND 
            Celle.IdxCella = PosizioneUdcCorrente.IdxCella INNER JOIN
            Blocchi ON Celle.IdxBlocco = Blocchi.IdxBlocco INNER JOIN
            AnagMag ON Blocchi.CodMag = AnagMag.CodMag AND Blocchi.CodCS = AnagMag.CodCS AND Blocchi.CodMag = AnagMag.CodMag AND 
            Blocchi.CodCS = AnagMag.CodCS AND Blocchi.CodMag = AnagMag.CodMag AND Blocchi.CodCS = AnagMag.CodCS ON 
            ElencoCartellini.UDC = PosizioneUdcCorrente.UDC
WHERE       (ElencoCartellini.CodCS = @CodCS) AND 
			((ElencoCartellini.UDC like '%'+ @searchVal+'%') OR (ElencoCartellini.Particolare like '%'+ @searchVal+'%') OR 
			(ElencoCartellini.RagSociale like '%'+ @searchVal+'%'))

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_getUdcDetailFullCode]'
GO

/***************************************
* STORED stp_getUdcDetailFullCode
*
* ottiene l'elenco degli UDC con i dati significativi partendo da un codice UDC COMPLETO
*
* Steamware, S.E.L.
* mod: 2010.05.24
*
****************************************/
ALTER PROCEDURE [dbo].[stp_getUdcDetailFullCode]
(
	@UDC VARCHAR(50),
	@CodCS VARCHAR(2)	
)
AS

SELECT        ec.UDC, ISNULL(ec.RagSociale, N'nd') AS RagSociale, ISNULL(ec.Particolare, N'nd') AS Particolare,
			  ISNULL(ec.DescParticolare, N'nd') AS DescParticolare, ISNULL(ec.DisegnoGrezzo, N'nd') AS DisegnoGrezzo, 
              ISNULL(ec.Esponente, N'nd') AS Esponente, ISNULL(ec.DescImpianto, N'nd') AS DescImpianto,
              ISNULL(ec.DataFus, N'') AS DataFus, ISNULL(ec.TurnoFus, 0) AS TurnoFus, ec.CodImballo, 
              ec.Qta,  ISNULL(AnagStatiProdotto.DescStato, N'nd') AS DescStato, ec.ModDate, 
              CASE WHEN(ec.IdxPosizione < 0)  THEN 'Consum' ELSE ISNULL(Blocchi.CodMag, N'nd') END AS CodMag, 
              ISNULL(Blocchi.CodBlocco, N'nd') AS CodBlocco, ISNULL(Celle.CodCella, N'nd') AS CodCella, ISNULL(dbo.Celle.IdxCella, 0) AS IdxCella,
              ISNULL(Celle.X, 0) AS X, ISNULL(Celle.Y, 0) AS Y, ISNULL(Celle.Z, 0) AS Z, ec.IdxPosizione, ISNULL(ec.Note,'') AS Note
FROM          AnagStatiProdotto RIGHT OUTER JOIN
              ElencoCartellini ec ON AnagStatiProdotto.CodStato = ec.CodStato AND AnagStatiProdotto.CodStato = ec.CodStato 
              LEFT OUTER JOIN Celle INNER JOIN PosizioneUdcCorrente ON Celle.IdxCella = PosizioneUdcCorrente.IdxCella 
              AND Celle.IdxCella = PosizioneUdcCorrente.IdxCella AND Celle.IdxCella = PosizioneUdcCorrente.IdxCella 
              INNER JOIN Blocchi ON Celle.IdxBlocco = Blocchi.IdxBlocco INNER JOIN AnagMag ON Blocchi.CodMag = AnagMag.CodMag 
              AND Blocchi.CodCS = AnagMag.CodCS AND Blocchi.CodMag = AnagMag.CodMag AND Blocchi.CodCS = AnagMag.CodCS AND Blocchi.CodMag = AnagMag.CodMag 
              AND Blocchi.CodCS = AnagMag.CodCS ON ec.UDC = PosizioneUdcCorrente.UDC
WHERE        (ec.UDC = @UDC) AND (ec.CodCS = @CodCS)

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_UdcDetailGetByIdxCella]'
GO

/***************************************
* STORED stp_UdcDetailGetByIdxCella
*
* ottiene l'elenco degli UDC con dettaglio per una data cella
*
* Steamware, S.E.L.
* mod: 2010.07.20
*
****************************************/
ALTER PROCEDURE [dbo].[stp_UdcDetailGetByIdxCella]
(
	@IdxCella INT
)
AS

SELECT		ElencoCartellini.UDC, ElencoCartellini.RagSociale, ElencoCartellini.Particolare, ElencoCartellini.DescParticolare, 
			ElencoCartellini.DisegnoGrezzo, ElencoCartellini.Esponente, ElencoCartellini.DescImpianto, ElencoCartellini.DataFus, 
			ElencoCartellini.TurnoFus, ElencoCartellini.CodImballo, ElencoCartellini.Qta, AnagStatiProdotto.DescStato, 
			ElencoCartellini.ModDate, ISNULL(Blocchi.CodMag, N'') AS CodMag, ISNULL(Blocchi.CodBlocco, N'') AS CodBlocco, 
			ISNULL(Celle.CodCella, N'') AS CodCella, ISNULL(dbo.Celle.IdxCella, 0) AS IdxCella, 
			ISNULL(Celle.X, 0) AS X, ISNULL(Celle.Y, 0) AS Y, ISNULL(Celle.Z, 0) AS Z, ElencoCartellini.IdxPosizione, ISNULL(ElencoCartellini.Note,'') AS Note
FROM        Celle INNER JOIN
            PosizioneUdcCorrente ON Celle.IdxCella = PosizioneUdcCorrente.IdxCella AND Celle.IdxCella = PosizioneUdcCorrente.IdxCella AND 
            Celle.IdxCella = PosizioneUdcCorrente.IdxCella INNER JOIN
            Blocchi ON Celle.IdxBlocco = Blocchi.IdxBlocco INNER JOIN
            AnagMag ON Blocchi.CodMag = AnagMag.CodMag AND Blocchi.CodCS = AnagMag.CodCS AND Blocchi.CodMag = AnagMag.CodMag AND 
            Blocchi.CodCS = AnagMag.CodCS AND Blocchi.CodMag = AnagMag.CodMag AND Blocchi.CodCS = AnagMag.CodCS INNER JOIN
            ElencoCartellini ON PosizioneUdcCorrente.UDC = ElencoCartellini.UDC LEFT OUTER JOIN
            AnagStatiProdotto ON ElencoCartellini.CodStato = AnagStatiProdotto.CodStato AND ElencoCartellini.CodStato = AnagStatiProdotto.CodStato
WHERE       (Celle.IdxCella = @IdxCella)

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_UdcDetailGetByIdxBlocco]'
GO

/***************************************
* STORED stp_UdcDetailGetByIdxBlocco
*
* ottiene l'elenco degli UDC con dettaglio posizioni OCCUPATE associati ad un dato blocco di magazzino
*
* Steamware, S.E.L.
* mod: 2010.07.19
*
****************************************/
ALTER PROCEDURE [dbo].[stp_UdcDetailGetByIdxBlocco]
(
	@IdxBlocco INT
)
AS

SELECT		ElencoCartellini.UDC, ElencoCartellini.RagSociale, ElencoCartellini.Particolare, ElencoCartellini.DescParticolare, 
			ElencoCartellini.DisegnoGrezzo, ElencoCartellini.Esponente, ElencoCartellini.DescImpianto, ElencoCartellini.DataFus, 
			ElencoCartellini.TurnoFus, ElencoCartellini.CodImballo, ElencoCartellini.Qta, AnagStatiProdotto.DescStato, 
			ElencoCartellini.ModDate, ISNULL(Blocchi.CodMag, N'') AS CodMag, ISNULL(Blocchi.CodBlocco, N'') AS CodBlocco, 
			ISNULL(Celle.CodCella, N'') AS CodCella, ISNULL(dbo.Celle.IdxCella, 0) AS IdxCella, 
			ISNULL(Celle.X, 0) AS X, ISNULL(Celle.Y, 0) AS Y, ISNULL(Celle.Z, 0) AS Z, ElencoCartellini.IdxPosizione, ISNULL(ElencoCartellini.Note,'') AS Note
FROM        Celle INNER JOIN
            PosizioneUdcCorrente ON Celle.IdxCella = PosizioneUdcCorrente.IdxCella AND Celle.IdxCella = PosizioneUdcCorrente.IdxCella AND 
            Celle.IdxCella = PosizioneUdcCorrente.IdxCella INNER JOIN
            Blocchi ON Celle.IdxBlocco = Blocchi.IdxBlocco INNER JOIN
            AnagMag ON Blocchi.CodMag = AnagMag.CodMag AND Blocchi.CodCS = AnagMag.CodCS AND Blocchi.CodMag = AnagMag.CodMag AND 
            Blocchi.CodCS = AnagMag.CodCS AND Blocchi.CodMag = AnagMag.CodMag AND Blocchi.CodCS = AnagMag.CodCS INNER JOIN
            ElencoCartellini ON PosizioneUdcCorrente.UDC = ElencoCartellini.UDC LEFT OUTER JOIN
            AnagStatiProdotto ON ElencoCartellini.CodStato = AnagStatiProdotto.CodStato AND ElencoCartellini.CodStato = AnagStatiProdotto.CodStato
WHERE       (Celle.IdxBlocco = @IdxBlocco)

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[Odette_storico]'
GO
ALTER TABLE [dbo].[Odette_storico] ALTER COLUMN [CampoUDC] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [i_UDC] on [dbo].[Odette_storico]'
GO
CREATE NONCLUSTERED INDEX [i_UDC] ON [dbo].[Odette_storico] ([CampoUDC])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[Odette]'
GO
ALTER TABLE [dbo].[Odette] ALTER COLUMN [CampoUDC] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_Odette] on [dbo].[Odette]'
GO
ALTER TABLE [dbo].[Odette] ADD CONSTRAINT [PK_Odette] PRIMARY KEY CLUSTERED  ([CampoUDC])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_getUDC_UnusedOlder]'
GO

-- Batch submitted through debugger: GMW_00401.sql|622|0|C:\Progetti\GMW\2011-12-14 vers test 401\Vers 401\GMW_00401.sql

ALTER PROCEDURE [dbo].[stp_getUDC_UnusedOlder]
(	
	@CodLista VARCHAR(12)
)
AS

	-- variabili locali che mi servono
	DECLARE @Figura AS VARCHAR(4)
	DECLARE @Esponente AS VARCHAR(6)
	DECLARE @CodImballo AS VARCHAR(15)
	DECLARE @Particolare AS VARCHAR (50)
	DECLARE @CodTipoLista AS VARCHAR (10)
	--DECLARE @IdxPosizione AS INT
	SET @Figura			= (SELECT Figura FROM ElencoListePrelievo WHERE CodLista = @CodLista)
	SET @Esponente		= (SELECT Esponente FROM ElencoListePrelievo WHERE CodLista = @CodLista)
	SET @CodImballo		= (SELECT CodImballo FROM ElencoListePrelievo WHERE CodLista = @CodLista)
	SET @Particolare	= (SELECT Particolare FROM ElencoListePrelievo WHERE CodLista = @CodLista) 
	SET @CodTipoLista	= (SELECT CodTipoLista FROM ElencoListePrelievo WHERE CodLista = @CodLista) 
	/*
	SET @IdxPosizione	= (SELECT	tt.IdxPosizione
	                 	   FROM     TipoListaPrelievo tlp INNER JOIN TabTranPosizEventi tt ON tlp.CodEvento = tt.CodEvento
	                 	   WHERE	(tlp.CodTipoLista = @CodTipoLista))
	*/

	-- effettuo la selezione
	SELECT		*
	FROM        ElencoCartellini
	WHERE       (Particolare = @Particolare) AND (IdxPosizione IN (SELECT	tt.IdxPosizione
	                 	   FROM     TipoListaPrelievo tlp INNER JOIN TabTranPosizEventi tt ON tlp.CodEvento = tt.CodEvento
	                 	   WHERE	(tlp.CodTipoLista = @CodTipoLista))) AND 
				(Figura		= CASE WHEN @Figura		= '*' THEN Figura		ELSE @Figura	END) AND
				(Esponente	= CASE WHEN @Esponente  = '*' THEN Esponente	ELSE @Esponente END) AND
				(CodImballo	= CASE WHEN @CodImballo	= '*' THEN CodImballo	ELSE @CodImballo END)				
				AND UDC NOT IN (
					SELECT DISTINCT UDC 
					FROM RigheListePrelievo INNER JOIN ElencoListePrelievo
					ON	RigheListePrelievo.CodLista = ElencoListePrelievo.CodLista
					WHERE (ElencoListePrelievo.CodLista = @CodLista) 
					OR (ElencoListePrelievo.CodLista <> @CodLista AND CodStatoLista > 1 AND Prelevato = 1)
					)
	ORDER BY	ElencoCartellini.DataFus

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[v_righeListePrelievoPosizione]'
GO
EXEC sp_refreshview N'[dbo].[v_righeListePrelievoPosizione]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[v_ArtInProd]'
GO
EXEC sp_refreshview N'[dbo].[v_ArtInProd]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[V_elencoListePrelievo]'
GO
EXEC sp_refreshview N'[dbo].[V_elencoListePrelievo]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_EDM_spostaDataMtx]'
GO

/*****************************************
* STORED stp_EDM_spostaDataMtx
*
* Sposta un datamatrix in un nuovo gitterbox
*
* Steamware, S.E.L.
* mod: 2011.05.05
*
****************************************/
ALTER PROCEDURE [dbo].[stp_EDM_spostaDataMtx]
(
	@CodDataMatrix NUMERIC(23),
	@CodGitterbox NVARCHAR(10),
	@CodSoggetto VARCHAR(17)
)
AS

-- salvo codice gitterbox di partenza
DECLARE @CodGitterboxFrom AS NVARCHAR(10)
SET		@CodGitterboxFrom = ( SELECT CodGitterbox FROM ElencoDataMatrix WHERE CodDataMatrix = @CodDataMatrix ) 

UPDATE	ElencoDataMatrix
SET		CodGitterbox = @CodGitterbox
WHERE	CodDataMatrix = @CodDataMatrix

-- aggiorno valori QTA gitterbox di partenza...
UPDATE	ElencoCartellini
SET		Qta = ( SELECT COUNT(*) FROM ElencoDataMatrix WHERE CodGitterbox = @CodGitterboxFrom )
WHERE	UDC = @CodGitterboxFrom
-- aggiorno valoti QTA gitterbox destinazione e modificatore...
UPDATE	ElencoCartellini
SET		Qta = ( SELECT COUNT(*) FROM ElencoDataMatrix WHERE CodGitterbox = @CodGitterbox ), CodSoggetto = @CodSoggetto, ModDate = GETDATE()
WHERE	UDC = @CodGitterbox

-- aggiorno righe liste di prelievo...
UPDATE	RigheListePrelievo
SET		Qta = ( SELECT COUNT(*) FROM ElencoDataMatrix WHERE CodGitterbox = @CodGitterboxFrom )
WHERE	UDC = @CodGitterboxFrom
-- aggiorno valoti QTA gitterbox destinazione...
UPDATE	RigheListePrelievo
SET		Qta = ( SELECT COUNT(*) FROM ElencoDataMatrix WHERE CodGitterbox = @CodGitterbox )
WHERE	UDC = @CodGitterbox

-- select finale!
SELECT	*
FROM	ElencoDataMatrix
WHERE	CodDataMatrix = @CodDataMatrix

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_EDM_svuotaGitterbox]'
GO

/*****************************************
* STORED stp_EDM_svuotaGitterbox
*
* Elimina il codice gitterbox dai datamatrix associati (svuotandolo...)
*
* Steamware, S.E.L.
* mod: 2011.05.06
*
****************************************/
ALTER PROCEDURE [dbo].[stp_EDM_svuotaGitterbox]
(
	@CodGitterbox NVARCHAR(10),
	@CodSoggetto VARCHAR(17)
)
AS

UPDATE	ElencoDataMatrix
SET		CodGitterbox = 'EmptyGBox'
WHERE	CodGitterbox = @CodGitterbox

-- aggiorno valoti QTA gitterbox originale...
UPDATE	ElencoCartellini
SET		Qta = ( SELECT COUNT(*) FROM ElencoDataMatrix WHERE CodGitterbox = @CodGitterbox )
WHERE	UDC = @CodGitterbox
-- aggiorno valoti QTA gitterbox 'EmptyGBox
UPDATE	ElencoCartellini
SET		Qta = ( SELECT COUNT(*) FROM ElencoDataMatrix WHERE CodGitterbox = 'EmptyGBox' ), CodSoggetto = @CodSoggetto, ModDate = GETDATE()
WHERE	UDC = 'EmptyGBox'

-- aggiorno righe liste di prelievo...
UPDATE	RigheListePrelievo
SET		Qta = ( SELECT COUNT(*) FROM ElencoDataMatrix WHERE CodGitterbox = @CodGitterbox )
WHERE	UDC = @CodGitterbox



RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_ODETTE_getByUDC]'
GO

/*****************************************
* STORED stp_ODETTE_getByUDC
*
* Recupera etichette odette da stampare (dato un codice UDC)
*
* Steamware, S.E.L.
* mod: 2011.12.19
*
****************************************/
ALTER PROCEDURE [dbo].[stp_ODETTE_getByUDC]
(
	@UDC NVARCHAR(50)
)
AS
 
/* calcolo il codice di raggrupp bolla da passare x generazione cartellino al report partendo da CodCS */
 
SELECT  Od.CampoUDC
       ,Od.Campo1_1
       ,Od.Campo1_2
       ,Od.Campo1_3
       ,Od.Campo1_4
       ,Od.Campo1_5
 
       ,Od.Campo2_1
       ,Od.Campo2_2
       ,Od.Campo2_3
       ,Od.Campo2_4
       ,Od.Campo2_5
 
       ,Od.Campo3_1
 
       -- Formatto il numero bolla in base alla tabella di decodifica
       ,  dbo.f_padLeft( ISNULL(dec.NumRaggrMag,''), 2, '0' )
        + dbo.f_padLeft( ISNULL(Od.GrpBolla    ,''), 1, '0' )
        + dbo.f_padLeft( ISNULL(Od.Campo3_2    ,''), 6, '0' ) AS Campo3_2
 
       ,Od.Campo3_3
       ,Od.Campo3_4
       ,Od.Campo3_5
 
       ,Od.Campo4_1
       ,Od.Campo4_2
       ,Od.Campo4_3
       ,Od.Campo4_4
       ,Od.Campo4_5
 
       ,Od.Campo5_1
       ,Od.Campo5_2
       ,Od.Campo5_3
       ,Od.Campo5_4
       ,Od.Campo5_5
 
       ,Od.Campo6_1
       ,Od.Campo6_2
       ,Od.Campo6_3
       ,Od.Campo6_4
       ,Od.Campo6_5
 
       ,Od.Campo7_1
       ,Od.Campo7_2
       ,Od.Campo7_3
       ,Od.Campo7_4
       ,Od.Campo7_5
 
       ,Od.Campo8_1
       ,Od.Campo8_2
       ,Od.Campo8_3
       ,Od.Campo8_4
       ,Od.Campo8_5
 
       ,Od.Campo9_1
       ,Od.Campo9_2
       ,Od.Campo9_3
       ,Od.Campo9_4
       ,Od.Campo9_5
 
       ,Od.Campo10_1
       ,Od.Campo10_2
       ,Od.Campo10_3
       ,Od.Campo10_4
       ,Od.Campo10_5
 
       ,Od.Campo11_1_1
       ,Od.Campo11_1_2
       ,Od.Campo11_1_3
       ,Od.Campo11_1_4
       ,Od.Campo11_1_5
       ,Od.Campo11_2_1
       ,Od.Campo11_2_2
       ,Od.Campo11_2_3
       ,Od.Campo11_2_4
       ,Od.Campo11_2_5
 
       ,Od.Campo12_1
       ,Od.Campo12_2
       ,Od.Campo12_3
       ,Od.Campo12_4
       ,Od.Campo12_5
 
       ,Od.Campo13_1
       ,Od.Campo13_2
       ,Od.Campo13_3
       ,Od.Campo13_4
       ,Od.Campo13_5
 
       ,Od.Campo14_1
       ,Od.Campo14_2
       ,Od.Campo14_3
       ,Od.Campo14_4
       ,Od.Campo14_5
 
       ,Od.Campo15_1
       ,Od.Campo15_2
       ,Od.Campo15_3
       ,Od.Campo15_4
       ,Od.Campo15_5
 
       ,Od.Campo16_1
       ,Od.Campo16_2
       ,Od.Campo16_3
       ,Od.Campo16_4
       ,Od.Campo16_5
 
       ,Od.StatoOk
       ,Od.CodCS
       ,Od.CodMag
       ,Od.GrpBolla
       ,Od.DataBolla
       ,Od.NumBolla
       ,Od.Particolare
       ,Od.CodLista
 
FROM     Odette AS Od
INNER JOIN RilPro.TabDecodBolla AS dec
ON Od.CodMag = dec.CodRaggrMag
WHERE ( Od.CampoUDC = @UDC )
RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_ODETTE_updateLingua]'
GO

/*****************************************
* STORED stp_ODETTE_updateLingua
*
* Salva nell'etichetta ODETTE indicata le stringhe della lingua desiderata
*
* Steamware, S.E.L.
* mod: 2010.10.22
*
****************************************/
ALTER PROCEDURE [dbo].[stp_ODETTE_updateLingua]
(
	@UDC NVARCHAR(50),
	@Campo1_1 NVARCHAR(50),
	@Campo2_1 NVARCHAR(50),
	@Campo3_1 NVARCHAR(50),
	@Campo4_1 NVARCHAR(50),
	@Campo5_1 NVARCHAR(50),
	@Campo6_1 NVARCHAR(50),
	@Campo7_1 NVARCHAR(50),
	@Campo8_1 NVARCHAR(50),
	@Campo9_1 NVARCHAR(50),
	@Campo10_1 NVARCHAR(50),
	@Campo11_1_1 NVARCHAR(50),
	@Campo11_2_1 NVARCHAR(50),
	@Campo12_1 NVARCHAR(50),
	@Campo13_1 NVARCHAR(50),
	@Campo14_1 NVARCHAR(50),
	@Campo15_1 NVARCHAR(50),
	@Campo16_1 NVARCHAR(50)
)
AS

UPDATE	Odette
SET		Campo1_1 = @Campo1_1,
		Campo2_1 = @Campo2_1,
		Campo3_1 = @Campo3_1,
		Campo4_1 = @Campo4_1,
		Campo5_1 = @Campo5_1,
		Campo6_1 = @Campo6_1,
		Campo7_1 = @Campo7_1,
		Campo8_1 = @Campo8_1,
		Campo9_1 = @Campo9_1,
		Campo10_1 = @Campo10_1,
		Campo11_1_1 = @Campo11_1_1,
		Campo11_2_1 = @Campo11_2_1,
		Campo12_1 = @Campo12_1,
		Campo13_1 = @Campo13_1,
		Campo14_1 = @Campo14_1,
		Campo15_1 = @Campo15_1,
		Campo16_1 = @Campo16_1
WHERE	CampoUDC = @UDC	

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_ODETTE_updateVal]'
GO

/*****************************************
* STORED stp_ODETTE_updateVal
*
* effettua aggiornamento valori etichetta Odette
*
* Steamware, S.E.L.
* mod: 2011.04.28
*
****************************************/
ALTER PROCEDURE [dbo].[stp_ODETTE_updateVal]
(
	@UDC NVARCHAR(50),
	@ragSocCli NVARCHAR(50),
	@indirCli NVARCHAR(50),
	@capCittCli NVARCHAR(50),
	@ragSocDest NVARCHAR(50),
	@indirDest NVARCHAR(50),
	@capCittDest NVARCHAR(50),
	@pesoNetto NVARCHAR(50),
	@pesoLordo NVARCHAR(50),
	@numColli NVARCHAR(50),
	@disPart NVARCHAR(50),
	@descPart NVARCHAR(50),
	@codPart NVARCHAR(50),
	@codImb NVARCHAR(50),
	@codMaz NVARCHAR(50),
	@codNael NVARCHAR(50)	
)
AS

-- COMMIT TRAN


-- update valori (sovrascrivo...)
UPDATE	Odette
SET		Campo1_2 = @ragSocCli,
		Campo1_3 = @indirCli,
		Campo1_4 = @capCittCli,
		Campo2_2 = @ragSocDest,
		Campo2_3 = @indirDest,
		Campo2_4 = @capCittDest,
		Campo5_2 = @pesoNetto,
		Campo6_2 = @pesoLordo,
		Campo7_2 = @numColli,
		Campo8_2 = @disPart,
		Campo8_3 = @disPart,
		Campo10_2 = @descPart,
		Campo11_1_2 = @codPart,
		Campo11_1_3 = @codPart,
		Campo11_2_2 = @codImb,
		Campo11_2_3 = @codImb,
		Campo12_2 = @codMaz,
		Campo12_3 = @codMaz,
		Campo14_2 = @codNael

WHERE	CampoUDC = @UDC

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_prtCartMatPrimaByUDC]'
GO

/***************************************
* STORED stp_prtCartMatPrimaByUDC
*
* ottiene il record del Cartellino Materia Prima dato l'UDC richiesto x la stampa
*
****************************************/
ALTER PROCEDURE [dbo].[stp_prtCartMatPrimaByUDC]
(
      @UDC VARCHAR(50)
)
AS
SELECT
        RQ.nRapQual,
        RQ.DataRapQual,
        RQ.ProgUDC,
        RQ.CodFor,
        RQ.DestTerz,
        RQ.CodLega,
        RQ.DataPrelFus,
        RQ.TurnoPrelFus,
        RQ.Qta,
        RQ.DestLega,
        RQ.LegaScaric,
        RQ.BenesQual,
        RQ.UDC,
        Fo.DescFornitore,
        Fo.DescFornitore2,
        Le.CodLega AS CodLega2,
        Le.DescLega,
        Le.DescLega2,
        ISNULL(ec.Note,'') AS Note
FROM RilPro.RapQual AS RQ
LEFT JOIN RilPro.AnagFornitori AS Fo
  ON RQ.CodFor = Fo.CodFornitore
LEFT JOIN RilPro.AnagLeghe Le
  ON RQ.CodLega = 'L' + Le.CodLega
LEFT JOIN ElencoCartellini ec
  ON RQ.UDC = ec.UDC
WHERE RQ.UDC = RTRIM(@UDC)                  -- 'UB31000000001'

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_ODETTE_setStato]'
GO

/*****************************************
* STORED stp_ODETTE_setStato
*
* aggiorna stato etichetta Odette
*
* Steamware, S.E.L.
* mod: 2011.04.29
*
****************************************/
ALTER PROCEDURE [dbo].[stp_ODETTE_setStato]
(
	@UDC NVARCHAR(50),
	@StatoOk BIT
)
AS

-- update valori (sovrascrivo...)
UPDATE	Odette
SET		StatoOk = @StatoOk
WHERE	CampoUDC = @UDC

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[stp_UDC_insNewFull_fixUDC]'
GO

/***************************************
* STORED stp_UDC_insNewFull_fixUDC
*
* crea un nuovo record nella TabStatoOdpUdc FULL (con pesi, qta...) avendo in entrata il codice UDC da utilizzare
*
* Steamware, S.E.L.
* mod: 2011.12.07
*
****************************************/
create PROCEDURE [dbo].[stp_UDC_insNewFull_fixUDC]
(
	@UDC AS NVARCHAR(50),
	@CodCS VARCHAR(2),
	@Flusso VARCHAR(2),
	@Anno VARCHAR(2),
	@CodCliente VARCHAR(6),
	@Particolare VARCHAR(15),
	@CodImpianto AS VARCHAR(50),
	@CodStampo VARCHAR(8),
	@Esponente VARCHAR(6),
	@Figura VARCHAR(4),
	@DataFus DATETIME,
	@TurnoFus INT,
	@CodImballo VARCHAR(15),
	@CodSoggetto VARCHAR(17),	
	@Tara FLOAT,
	@IdxPosizione INT,
	@CodTipoDichiaraz CHAR(1),
	@CodEvento VARCHAR(10),
	@Qta DECIMAL(10,2),
	@PesoTot FLOAT,
	@PesoCad FLOAT,
	@CodStato VARCHAR(50),
	@UDC_parent VARCHAR(50),
	@DoDeleteUdcParent BIT,
	@Note NVARCHAR(500)
)
AS

-- DECLARE iniziali
DECLARE @RagSociale AS VARCHAR(35)
DECLARE @DescParticolare AS VARCHAR(30)
DECLARE @DescImpianto AS VARCHAR(50)
DECLARE @DisegnoGrezzo AS VARCHAR(30)
DECLARE @NumCont AS INT
DECLARE @numFlu AS INT
DECLARE @numUdc AS INT
DECLARE @nextUdc AS INT
DECLARE @IdxCella AS INT

------------------------------------------------------------------------------------------------------
-- Caricamento Dati da anagrafica
------------------------------------------------------------------------------------------------------
-- cerco la ragione sociale...
SET @RagSociale = (
	SELECT        ISNULL(RagSociale, 'ND') AS RagSociale
	FROM            RilPro.AnagClienti
	WHERE        (CodCliente = @CodCliente)
)

-- Cerco descrizione del particolare...
SET @DescParticolare = (
	SELECT        ISNULL(DescParticolare, 'ND') AS DescParticolare
	FROM            RilPro.AnagParticolari
	WHERE        (Particolare = @Particolare)
)

-- cerco codice disegno grezzo
SET @DisegnoGrezzo = (
	SELECT        ISNULL(DisegnoGrezzo, 'ND') AS DescParticolare
	FROM            RilPro.AnagParticolari
	WHERE        (Particolare = @Particolare)
)

-- cerco descrizione Impianto
SET @DescImpianto = (
	SELECT        ISNULL(DescImpianto, 'ND') AS DescImpianto
	FROM            AnagImpianti
	WHERE        (CodImpianto = @CodImpianto)
)



-- Calcolo contatore intero successivo x il contenitore...
SET @NumCont = (
	SELECT      ISNULL(MAX(NumCont), 0) + 1 AS NumCont
	FROM        ElencoCartellini
	WHERE       (Particolare = @Particolare)
	AND			(DataFus = @DataFus)
	AND			(TurnoFus = @TurnoFus)
	AND			(CodImpianto = @CodImpianto)
	AND			(CodStampo = @CodStampo)
	AND			(Figura = @Figura)	
)


------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------
-- Ciclo principale x inserimento nuovo record
------------------------------------------------------------------------------------------------------
BEGIN TRAN
		
	-- inserisco un nuovo record
	INSERT INTO ElencoCartellini(UDC, CodCS, CodCliente, RagSociale, Particolare, DescParticolare, DisegnoGrezzo, Esponente, CodImpianto, DescImpianto, CodStampo, Figura, DataFus, TurnoFus, CodImballo, CodSoggetto, NumCont, Tara, Qta, CodStato, IdxPosizione, PesoTot, PesoCad, CreateDate, ModDate, Note)
	VALUES	(@UDC, @CodCS, @CodCliente, @RagSociale, @Particolare, @DescParticolare, @DisegnoGrezzo, @Esponente, @CodImpianto, @DescImpianto, @CodStampo, @Figura, @DataFus, @TurnoFus, @CodImballo, @CodSoggetto, @NumCont, @Tara, @Qta, @CodStato, @IdxPosizione, @PesoTot, @PesoCad, GETDATE(), GETDATE(), @Note)

	-- inserisco relazione parent-child tra UDC vecchio (tara) e nuovo (pesa) SSE UDC_parent valido
	IF((NOT @UDC_parent IS NULL) AND (NOT @UDC_parent = ''))
	BEGIN
		INSERT INTO RelazUDC(UDC_parent, UDC_child)
		VALUES (@UDC_parent, @UDC)
	END
COMMIT TRAN

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- Ciclo secondario x eventuale cancellazione UDC tara parent e inserimento in posizione
------------------------------------------------------------------------------------------------------
	-- se richiesto elimino l'UDC parent (tara)
	IF(@DoDeleteUdcParent = 1)
		BEGIN
			DELETE FROM ElencoCartellini WHERE UDC = @UDC_parent
		END
	
	-- calcolo la prima cella della posizione indicata..
	SET @IdxCella = (
					SELECT TOP 1 c.IdxCella 
						FROM Celle c INNER JOIN Blocchi b ON c.IdxBlocco=b.IdxBlocco 
						WHERE b.CodMag = CAST(@IdxPosizione AS NVARCHAR(50))
					)
	-- inserisco il cartellino nella prima cella della posizione indicata..
	INSERT INTO PosizioneUdcCorrente
	VALUES (@UDC, @IdxCella, @CodCS, GETDATE())
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- restituisco la tab dati con l'udc appena inserito
------------------------------------------------------------------------------------------------------
SELECT * 
FROM ElencoCartellini
WHERE  UDC = @UDC
------------------------------------------------------------------------------------------------------

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_UDC_insNewFull]'
GO

/***************************************
* STORED stp_UDC_insNewFull
*
* crea un nuovo record nella TabStatoOdpUdc FULL (con pesi, qta...) e restituisce il codice UDC appena creato
*
* Steamware, S.E.L.
* mod: 2010.04.28
*
****************************************/
ALTER PROCEDURE [dbo].[stp_UDC_insNewFull]
(
	@CodCS VARCHAR(2),
	@Flusso VARCHAR(2),
	@Anno VARCHAR(2),
	@CodCliente VARCHAR(6),
	@Particolare VARCHAR(15),
	@CodImpianto AS VARCHAR(50),
	@CodStampo VARCHAR(8),
	@Esponente VARCHAR(6),
	@Figura VARCHAR(4),
	@DataFus DATETIME,
	@TurnoFus INT,
	@CodImballo VARCHAR(15),
	@CodSoggetto VARCHAR(17),	
	@Tara FLOAT,
	@IdxPosizione INT,
	@CodTipoDichiaraz CHAR(1),
	@CodEvento VARCHAR(10),
	@Qta DECIMAL(10,2),
	@PesoTot FLOAT,
	@PesoCad FLOAT,
	@CodStato VARCHAR(50),
	@UDC_parent VARCHAR(50),
	@DoDeleteUdcParent BIT,
	@Note NVARCHAR(500)
)
AS

-- DECLARE iniziali
DECLARE @UDC AS VARCHAR(50)
DECLARE @RagSociale AS VARCHAR(35)
DECLARE @DescParticolare AS VARCHAR(30)
DECLARE @DescImpianto AS VARCHAR(50)
DECLARE @DisegnoGrezzo AS VARCHAR(30)
DECLARE @NumCont AS INT
DECLARE @numFlu AS INT
DECLARE @numUdc AS INT
DECLARE @nextUdc AS INT
DECLARE @IdxCella AS INT

------------------------------------------------------------------------------------------------------
-- Caricamento Dati da anagrafica
------------------------------------------------------------------------------------------------------
-- cerco la ragione sociale...
SET @RagSociale = (
	SELECT        ISNULL(RagSociale, 'ND') AS RagSociale
	FROM            RilPro.AnagClienti
	WHERE        (CodCliente = @CodCliente)
)

-- Cerco descrizione del particolare...
SET @DescParticolare = (
	SELECT        ISNULL(DescParticolare, 'ND') AS DescParticolare
	FROM            RilPro.AnagParticolari
	WHERE        (Particolare = @Particolare)
)

-- cerco codice disegno grezzo
SET @DisegnoGrezzo = (
	SELECT        ISNULL(DisegnoGrezzo, 'ND') AS DescParticolare
	FROM            RilPro.AnagParticolari
	WHERE        (Particolare = @Particolare)
)

-- cerco descrizione Impianto
SET @DescImpianto = (
	SELECT        ISNULL(DescImpianto, 'ND') AS DescImpianto
	FROM            AnagImpianti
	WHERE        (CodImpianto = @CodImpianto)
)



-- Calcolo contatore intero successivo x il contenitore...
SET @NumCont = (
	SELECT      ISNULL(MAX(NumCont), 0) + 1 AS NumCont
	FROM        ElencoCartellini
	WHERE       (Particolare = @Particolare)
	AND			(DataFus = @DataFus)
	AND			(TurnoFus = @TurnoFus)
	AND			(CodImpianto = @CodImpianto)
	AND			(CodStampo = @CodStampo)
	AND			(Figura = @Figura)	
)


------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- Flusso
------------------------------------------------------------------------------------------------------
-- controllo se esiste il flusso (bilancia)...
SET @numFlu = (
	SELECT count(*)
	FROM AnagBilance
	WHERE	CodBilancia	= @Flusso
)
-- ...senn lo creo...
IF(@numFlu = 0)
	BEGIN
		-- se c' inserisco in tab
		INSERT INTO AnagBilance(CodBilancia, DescrImpianto, CodCS)
		VALUES (@Flusso, @Flusso, @CodCS)
	END
------------------------------------------------------------------------------------------------------

		
------------------------------------------------------------------------------------------------------
-- UDC
------------------------------------------------------------------------------------------------------
-- controllo se ci sia gi un UDC per company / flusso anno
BEGIN TRAN
	-- cerco nella tab contatori UDC l'ultimo valido
	SET @numUdc = (
		SELECT count(*)
		FROM ContatoriUdc
		WHERE	CodCS		=	@CodCS
		AND		Flusso		=	@Flusso
		AND		Anno		=	@Anno
	)
	-- controllo se record c'...
	IF(@numUdc > 0)
		BEGIN
			-- se c' incremento di 1 in tab
			UPDATE ContatoriUdc
			SET LastIdx = LastIdx  + 1
			WHERE	CodCS		=	@CodCS
			AND		Flusso		=	@Flusso
			AND		Anno		=	@Anno
		END
	ELSE
		BEGIN
			-- lo creo!
			INSERT INTO ContatoriUdc(CodCS, Flusso, Anno, LastIdx)
			VALUES (@CodCS, @Flusso, @Anno, 1)
		END
	-- aggiorno numero UDC
	SET @numUdc = (
		SELECT LastIdx
		FROM ContatoriUdc
		WHERE	CodCS		=	@CodCS
		AND		Flusso		=	@Flusso
		AND		Anno		=	@Anno
	)
COMMIT TRAN
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- Ciclo principale x inserimento nuovo record
------------------------------------------------------------------------------------------------------
BEGIN TRAN
	-- ottengo il nuovo codice UDC completo
	--SET @UDC = 'U' + @CodCS + @Flusso + @Anno + dbo.f_padLeft(CAST(@numUdc AS VARCHAR(6)), 6, '0')
	SET @UDC = dbo.componiUdc(@CodCS, @Flusso, @Anno, @numUdc)

		
	-- inserisco un nuovo record
	INSERT INTO ElencoCartellini(UDC, CodCS, CodCliente, RagSociale, Particolare, DescParticolare, DisegnoGrezzo, Esponente, CodImpianto, DescImpianto, CodStampo, Figura, DataFus, TurnoFus, CodImballo, CodSoggetto, NumCont, Tara, Qta, CodStato, IdxPosizione, PesoTot, PesoCad, CreateDate, ModDate, Note)
	VALUES	(@UDC, @CodCS, @CodCliente, @RagSociale, @Particolare, @DescParticolare, @DisegnoGrezzo, @Esponente, @CodImpianto, @DescImpianto, @CodStampo, @Figura, @DataFus, @TurnoFus, @CodImballo, @CodSoggetto, @NumCont, @Tara, @Qta, @CodStato, @IdxPosizione, @PesoTot, @PesoCad, GETDATE(), GETDATE(), @Note)

	-- inserisco relazione parent-child tra UDC vecchio (tara) e nuovo (pesa) SSE UDC_parent valido
	IF((NOT @UDC_parent IS NULL) AND (NOT @UDC_parent = ''))
	BEGIN
		INSERT INTO RelazUDC(UDC_parent, UDC_child)
		VALUES (@UDC_parent, @UDC)
	END
COMMIT TRAN

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- Ciclo secondario x eventuale cancellazione UDC tara parent e inserimento in posizione
------------------------------------------------------------------------------------------------------
	-- se richiesto elimino l'UDC parent (tara)
	IF(@DoDeleteUdcParent = 1)
		BEGIN
			DELETE FROM ElencoCartellini WHERE UDC = @UDC_parent
		END
	
	-- calcolo la prima cella della posizione indicata..
	SET @IdxCella = (
					SELECT TOP 1 c.IdxCella 
						FROM Celle c INNER JOIN Blocchi b ON c.IdxBlocco=b.IdxBlocco 
						WHERE b.CodMag = CAST(@IdxPosizione AS NVARCHAR(50))
					)
	-- inserisco il cartellino nella prima cella della posizione indicata..
	INSERT INTO PosizioneUdcCorrente
	VALUES (@UDC, @IdxCella, @CodCS, GETDATE())
------------------------------------------------------------------------------------------------------

/*
------------------------------------------------------------------------------------------------------
-- Salvataggio dati evento in tab StoricoEventi
------------------------------------------------------------------------------------------------------
INSERT INTO StoricoEventi(DataEv, CodEvento, CodOperatore, CodTipoDichiaraz, CodCS, UDC, Qta, PesoTot, PesoCad, Particolare, CodStato, CodStampo, Figura, FiguraIncisa)
VALUES	(GETDATE(), @CodEvento, @CodOperatore, @CodTipoDichiaraz, @CodCS, @UDC, @Qta, @PesoTot, @PesoCad, @Particolare, @CodStato, @CodStampo, @Figura, @FiguraIncisa)
------------------------------------------------------------------------------------------------------
*/

------------------------------------------------------------------------------------------------------
-- restituisco la tab dati con l'udc appena inserito
------------------------------------------------------------------------------------------------------
SELECT * 
FROM ElencoCartellini
WHERE  UDC = @UDC
------------------------------------------------------------------------------------------------------

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[V_MagazziniLogiciOverview]'
GO
EXEC sp_refreshview N'[dbo].[V_MagazziniLogiciOverview]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_UDC_insNew]'
GO

/***************************************
* STORED stp_UDC_insNew
*
* crea un nuovo record nella TabStatoOdpUdc e restituisce il codice UDC appena creato
*
* Steamware, S.E.L.
* mod: 2010.04.28
*
****************************************/
ALTER PROCEDURE [dbo].[stp_UDC_insNew]
(
	@CodCS VARCHAR(2),
	@Flusso VARCHAR(2),
	@Anno VARCHAR(2),
	@Tara FLOAT,
	@CodImballo AS VARCHAR(50),
	@CodTipoDichiaraz CHAR(1),
	@CodSoggetto VARCHAR(17)
)
AS

-- DECLARE iniziali
DECLARE @UDC AS VARCHAR(50)
DECLARE @numFlu AS INT
DECLARE @numUdc AS INT
DECLARE @nextUdc AS INT
	
------------------------------------------------------------------------------------------------------
-- Flusso
------------------------------------------------------------------------------------------------------
-- controllo se esiste il flusso (bilancia)...
SET @numFlu = (
	SELECT count(*)
	FROM AnagBilance
	WHERE	CodBilancia	= @Flusso
)
-- ...senn lo creo...
IF(@numFlu = 0)
	BEGIN
		-- se c' inserisco in tab
		INSERT INTO AnagBilance(CodBilancia, DescrImpianto, CodCS)
		VALUES (@Flusso, @Flusso, @CodCS)
	END
------------------------------------------------------------------------------------------------------

		
------------------------------------------------------------------------------------------------------
-- UDC
------------------------------------------------------------------------------------------------------
-- controllo se ci sia gi un UDC per company / flusso anno
BEGIN TRAN
	-- cerco nella tab contatori UDC l'ultimo valido
	SET @numUdc = (
		SELECT count(*)
		FROM ContatoriUdc
		WHERE	CodCS		=	@CodCS
		AND		Flusso		=	@Flusso
		AND		Anno		=	@Anno
	)
	-- controllo se record c'...
	IF(@numUdc > 0)
		BEGIN
			-- se c' incremento di 1 in tab
			UPDATE ContatoriUdc
			SET LastIdx = LastIdx  + 1
			WHERE	CodCS		=	@CodCS
			AND		Flusso		=	@Flusso
			AND		Anno		=	@Anno
		END
	ELSE
		BEGIN
			-- lo creo!
			INSERT INTO ContatoriUdc(CodCS, Flusso, Anno, LastIdx)
			VALUES (@CodCS, @Flusso, @Anno, 1)
		END		
	-- aggiorno numero UDC
	SET @numUdc = (
		SELECT LastIdx
		FROM ContatoriUdc
		WHERE	CodCS		=	@CodCS
		AND		Flusso		=	@Flusso
		AND		Anno		=	@Anno
	)
COMMIT TRAN
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- Ciclo principale x inserimento nuovo record
------------------------------------------------------------------------------------------------------
-- ottengo il nuovo codice UDC completo
--SET @UDC = 'U' + @CodCS + @Flusso + @Anno + dbo.f_padLeft(CAST(@numUdc AS VARCHAR(6)), 6, '0')
SET @UDC = dbo.componiUdc(@CodCS, @Flusso, @Anno, @numUdc)
	
-- inserisco un nuovo record
INSERT INTO ElencoCartellini(CodCS, UDC, Tara, IdxPosizione, Qta, CodImballo, CodSoggetto, CreateDate, ModDate)
VALUES	(@CodCS, @UDC, @Tara, 0, 0, @CodImballo, @CodSoggetto, GETDATE(), GETDATE())
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- restituisco la tab dati con l'udc appena inserito
------------------------------------------------------------------------------------------------------
SELECT * 
FROM ElencoCartellini
WHERE  UDC = @UDC
------------------------------------------------------------------------------------------------------

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[v_selMagLogico]'
GO
EXEC sp_refreshview N'[dbo].[v_selMagLogico]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[v_datiPackingList]'
GO
EXEC sp_refreshview N'[dbo].[v_datiPackingList]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_UdcDetailGetByIdxBloccoCodMagLog]'
GO

/***************************************
* STORED stp_UdcDetailGetByIdxBloccoCodMagLog
*
* ottiene l'elenco degli UDC con dettaglio posizioni OCCUPATE associati ad un dato blocco di magazzino ed ad un dato STATO LOGICO
*
* Steamware, S.E.L.
* mod: 2010.09.15
*
****************************************/
ALTER PROCEDURE [dbo].[stp_UdcDetailGetByIdxBloccoCodMagLog]
(
	@IdxBlocco INT,
	@CodMagLog INT
)
AS

SELECT		ElencoCartellini.UDC, ElencoCartellini.RagSociale, ElencoCartellini.Particolare, ElencoCartellini.DescParticolare, 
			ElencoCartellini.DisegnoGrezzo, ElencoCartellini.Esponente, ElencoCartellini.DescImpianto, ElencoCartellini.DataFus, 
			ElencoCartellini.TurnoFus, ElencoCartellini.CodImballo, ElencoCartellini.Qta, AnagStatiProdotto.DescStato, 
			ElencoCartellini.ModDate, ISNULL(Blocchi.CodMag, N'') AS CodMag, ISNULL(Blocchi.CodBlocco, N'') AS CodBlocco, 
			ISNULL(Celle.CodCella, N'') AS CodCella, ISNULL(dbo.Celle.IdxCella, 0) AS IdxCella, 
			ISNULL(Celle.X, 0) AS X, ISNULL(Celle.Y, 0) AS Y, ISNULL(Celle.Z, 0) AS Z, ElencoCartellini.IdxPosizione, ISNULL(ElencoCartellini.Note,'') AS Note
FROM        Celle INNER JOIN
            PosizioneUdcCorrente ON Celle.IdxCella = PosizioneUdcCorrente.IdxCella AND Celle.IdxCella = PosizioneUdcCorrente.IdxCella AND 
            Celle.IdxCella = PosizioneUdcCorrente.IdxCella INNER JOIN
            Blocchi ON Celle.IdxBlocco = Blocchi.IdxBlocco INNER JOIN
            AnagMag ON Blocchi.CodMag = AnagMag.CodMag AND Blocchi.CodCS = AnagMag.CodCS AND Blocchi.CodMag = AnagMag.CodMag AND 
            Blocchi.CodCS = AnagMag.CodCS AND Blocchi.CodMag = AnagMag.CodMag AND Blocchi.CodCS = AnagMag.CodCS INNER JOIN
            ElencoCartellini ON PosizioneUdcCorrente.UDC = ElencoCartellini.UDC LEFT OUTER JOIN
            AnagStatiProdotto ON ElencoCartellini.CodStato = AnagStatiProdotto.CodStato AND ElencoCartellini.CodStato = AnagStatiProdotto.CodStato
WHERE       (Celle.IdxBlocco = @IdxBlocco) AND ElencoCartellini.IdxPosizione = @CodMagLog

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_spostaUdc]'
GO

/***************************************
* STORED stp_spostaUdc
*
* sposta un UDC, salvandone la posizione precedente (se esiste) nella tab storica
*
* Steamware, S.E.L.
* mod: 2010.06.11
*
****************************************/
ALTER PROCEDURE [dbo].[stp_spostaUdc]
(
	@CodCS VARCHAR(2),
	@UDC VARCHAR(50),
	@IdxCellaTo INT,
	@resetRLP BIT
)
AS

BEGIN TRAN
	
	-- DICHIARAZIONI iniziali variabili
	DECLARE @IdxCellaFrom INT
	DECLARE @capienza INT
	DECLARE @numUdc INT
	
	-- controllo cella di partenza
	SET		@IdxCellaFrom = (SELECT IdxCella FROM PosizioneUdcCorrente WHERE UDC = @UDC)
	-- controllo se la cella di dest vada indicata come piena
	SET		@capienza = (
							SELECT tc.Capienza 
								FROM TipoCella TC INNER JOIN Celle c ON TC.IdxTipoCella=c.IdxTipoCella
								WHERE c.IdxCella = @IdxCellaFrom	
						)
	SET @numUdc = (SELECT COUNT(*) FROM PosizioneUdcCorrente WHERE IdxCella = @IdxCellaFrom)
	-- se capienza raggiunta aggiorno...
	IF(@numUdc -1 < @capienza)
		BEGIN
			EXEC stp_celle_updPiena @IdxCellaFrom, 0
		END
		
	-- cancello posizione occupata
	DELETE
	FROM	PosizioneUdcCorrente
	WHERE	UDC = @UDC
	
	-- SE RICHIESTO cancello eventuali righe liste di prelievo FUSI per l'UDC
	IF(@resetRLP = 1)
	BEGIN
		DELETE FROM RigheListePrelievo
			WHERE UDC = @UDC AND CodLista IN ( 
				SELECT CodLista FROM ElencoListePrelievo 
				--WHERE CodTipoLista='01-PreFus'
				WHERE CodStatoLista < 4
			)
	END

	
	
	-- creo una nuova posizione per l'UDC
	INSERT INTO PosizioneUdcCorrente(UDC, IdxCella, CodCS, DataRif)
	VALUES(@UDC, @IdxCellaTo, @CodCS, GETDATE())
	
	-- controllo se la cella di dest vada indicata come piena
	SET		@capienza = (
							SELECT tc.Capienza 
								FROM TipoCella TC INNER JOIN Celle c ON TC.IdxTipoCella=c.IdxTipoCella
								WHERE c.IdxCella = @IdxCellaTo	
						)
	SET @numUdc = (SELECT COUNT(*) FROM PosizioneUdcCorrente WHERE IdxCella = @IdxCellaTo)
	-- se capienza raggiunta aggiorno...
	IF(@numUdc >= @capienza)
		BEGIN
			EXEC stp_celle_updPiena @IdxCellaTo, 1
		END
	
COMMIT TRAN

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_riattivaUdc]'
GO

/***************************************
* STORED stp_riattivaUdc
*
* riattiva un dato UDC (da mag consumato negativo a corrispettivo positivo) --> se era positivo NON FA NULLA!!! (perch uso ABS e controllo sia < 0)
*
* Steamware, S.E.L.
* mod: 2010.11.04
*
****************************************/
ALTER PROCEDURE [dbo].[stp_riattivaUdc]
(
	@UDC VARCHAR(50),
	@CodSoggetto NVARCHAR(17)
)
AS

DECLARE @CodEvento VARCHAR(10)
DECLARE @CodTipoDichiaraz VARCHAR(1)
DECLARE @IdxCella INT
DECLARE @CodCS NVARCHAR(2)

SET @CodEvento = 'UDC_RIA'
SET @CodTipoDichiaraz = 'U'

BEGIN TRAN
	
	-- faccio update posizione
	UPDATE      ElencoCartellini
	SET         IdxPosizione = ABS(IdxPosizione), ModDate=GETDATE(), CodSoggetto = @CodSoggetto
	WHERE		UDC = @UDC AND IdxPosizione IN (SELECT IdxPosizione FROM AnagPosizioni WHERE (IsRiattivaEnabled = 1)
)
	
	-- lo elimino da eventuali precedenti liste di prelievo senn poi non potrei + prelevarlo
	DELETE FROM RigheListePrelievo
	WHERE UDC = @UDC
	
	-- imposto posizione CELLA corrente x l'udc nella prima libera 
	SET @IdxCella = (SELECT c.IdxCella FROM Celle c inner join Blocchi b ON c.IdxBlocco=b.IdxBlocco INNER JOIN ElencoCartellini e ON b.CodMag = CAST(e.IdxPosizione AS NVARCHAR(50)) WHERE e.UDC = @UDC)
	SET @CodCS = (SELECT CodCS FROM ElencoCartellini WHERE UDC = @UDC)
	INSERT INTO PosizioneUdcCorrente
	VALUES(@Udc, @IdxCella, @CodCS, GETDATE())

	
COMMIT TRAN

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_ODETTE_upsertUdc]'
GO

/*****************************************
* STORED stp_ODETTE_upsertUdc
*
* effettua upsert x l'UDC (ovver crea nuovo solo se non c'...)
*
* Steamware, S.E.L.
* mod: 2011.04.28
*
****************************************/
ALTER PROCEDURE [dbo].[stp_ODETTE_upsertUdc]
(
	@UDC NVARCHAR(50),
	@CodCS VARCHAR(2),
	@numBolla NVARCHAR(6),
	@dataBolla NVARCHAR(8),
	@CodMag NVARCHAR(2),
    @GrpBolla NVARCHAR(1),
    @Particolare NVARCHAR(15),
    @CodLista NVARCHAR(12),
	@IndStabFrom NVARCHAR(50),
	@qtaCont NVARCHAR(50)
)
AS


-- DECLARE iniziali x gestione contatori odette
DECLARE @Flusso AS VARCHAR(2)
DECLARE @Anno AS VARCHAR(2)
DECLARE @numOdette AS INT
DECLARE @nextOdette AS INT
     
------------------------------------------------------------------------------------------------------
-- Fix flusso ed anno x 'OD' e '00' (contatore non spezzato su anno...
------------------------------------------------------------------------------------------------------
SET @Flusso = 'OD'
SET @Anno = '00'
           
------------------------------------------------------------------------------------------------------
-- Contatore Odette
------------------------------------------------------------------------------------------------------
-- controllo se ci sia gi un odette per company / flusso / anno
BEGIN TRAN
      -- cerco nella tab contatori UDC l'ultimo valido
      SET @numOdette = (
            SELECT		count(*)
            FROM		Odette
            WHERE		CodCS       =     @CodCS
      )
      -- controllo se record c'...
      IF(@numOdette > 0)
            BEGIN
                  -- se c' incremento di 1 in tab
                  UPDATE	ContatoriUdc
                  SET		LastIdx		= LastIdx  + 1
                  WHERE		CodCS       = @CodCS
                  AND       Flusso      = @Flusso
                  AND       Anno        = @Anno
            END
      ELSE
            BEGIN
                  -- lo creo!
                  INSERT INTO ContatoriUdc(CodCS, Flusso, Anno, LastIdx)
                  VALUES (@CodCS, @Flusso, @Anno, 1)
            END        
      -- aggiorno numero UDC
      SET @numOdette = (
            SELECT LastIdx
            FROM		ContatoriUdc
            WHERE		CodCS       = @CodCS
            AND         Flusso      = @Flusso
            AND         Anno        = @Anno
      )
COMMIT TRAN

-- cerco se ci sia gi la riga Odette
DECLARE @udcTrovati AS INT
SET		@udcTrovati = ( SELECT COUNT(*) FROM Odette WHERE CampoUDC = @UDC )

-- controllo se esista gi... se non c' creo
IF (@udcTrovati = 0)
	BEGIN
		INSERT INTO Odette(CampoUDC, StatoOk, CodCS)
		VALUES(@UDC, 0, @CodCS)
	END

-- calcolo valori x update
DECLARE @DataB AS NVARCHAR(7)
SET		@DataB = ( SELECT 'D'+ SUBSTRING(@dataBolla, 3, 6) )
DECLARE @udcShort NVARCHAR(10)
SET		@udcShort = dbo.f_onlyNumbers(@UDC)
	
-- update valori (sovrascrivo...)
UPDATE	Odette
SET		Campo3_2	= @numBolla,
		Campo3_3	= @numBolla,
		Campo4_2	= @IndStabFrom,
		Campo9_2	= @qtaCont,
		Campo9_3	= @qtaCont,
		Campo13_2	= @DataB,
		Campo15_2	= @numOdette,
		Campo15_3	= @numOdette,
		Campo16_2	= @udcShort, --@UDC, -- DA VERICARE, mettere UDC da anno in poi?!? @udcShort calcolato sopra? magari  sbagliato... 
		CodMag		= @CodMag,
		GrpBolla	= @GrpBolla,
		DataBolla	= CAST(@dataBolla AS NUMERIC(8,0)),
		NumBolla	= CAST(@numBolla AS NUMERIC(6,0)),
		Particolare	= @Particolare,
		CodLista	= @CodLista
WHERE	CampoUDC	= @UDC

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[f_padRight]'
GO

/***************************************
* FUNCTION f_padLeft
*
* fornisce una stringa della lunghezza desiderata aggiungendo a sx il carattere richiesto alla @string originale
*
* Steamware, S.E.L.
* mod: 2010.03.19
*
****************************************/
create FUNCTION [dbo].[f_padRight] (@string VARCHAR(255), @desired_length INTEGER, @pad_character CHAR(1))
RETURNS VARCHAR(255) AS  
BEGIN

 -- Prefix the required number of spaces to bulk up the string and then replace the spaces with the desired character
 RETURN CASE
          WHEN LEN(@string) < @desired_length
            THEN @string + REPLACE(SPACE(@desired_length - LEN(@string)), ' ', @pad_character)
          ELSE @string
        END

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[v_transcParticolari]'
GO
EXEC sp_refreshview N'[dbo].[v_transcParticolari]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_updateByBilancia]'
GO



/***************************************
* STORED stp_updateByBilancia
*
* aggiorna i dati a partire dalla segnalazione conteggio pezzi della bilancia
*
* Steamware, S.E.L.
* mod: 2010.03.19
*
****************************************/
ALTER PROCEDURE [dbo].[stp_updateByBilancia]
(
	@CodCompany VARCHAR(4),
	@UDC VARCHAR(50),
	@CodArticolo VARCHAR(50),
	@qta INT
)
AS

------------------------------------------------------------------------------------------------------
-- Articoli
------------------------------------------------------------------------------------------------------
DECLARE @numArt AS INT
-- controllo se esista il cod articolo o lo creo
SET @numArt = (
	SELECT count(*)
	FROM AnagArticoli
	WHERE	CodArticolo	=	@CodArticolo
)
-- ...senn lo creo...
IF(@numArt = 0)
	BEGIN
		INSERT INTO AnagArticoli(CodArticolo, DescrArticolo, Figura)
		VALUES (@CodArticolo, @CodArticolo, 'ND')
	END
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- ODP = articolo
------------------------------------------------------------------------------------------------------
DECLARE @numOdp AS INT
-- controllo se esiste l'ODP = articolo...
SET @numOdp = (
	SELECT count(*)
	FROM AnagOdp
	WHERE	CodCompany	=	@CodCompany
	AND		ODP		=	@CodArticolo
)
-- ...senn lo creo...
IF(@numOdp = 0)
	BEGIN
		-- se c' inserisco in tab
		INSERT INTO AnagOdp(CodCompany, ODP, CodArticolo, Esponente, CodDisegno, Magazzino, Ubicazione, CreateDate, ModDate)
		VALUES (@CodCompany, @CodArticolo, @CodArticolo, 'ND', 'ND', 'WIP', 'ND', GETDATE(), GETDATE())
	END
------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------
-- UDC
------------------------------------------------------------------------------------------------------
-- aggiorno lo stato UDC
UPDATE	TabStatoOdpUdc
SET		Qta = @Qta, ODP = @CodArticolo
WHERE	UDC = @UDC
------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------
-- restituisco la tab dati con l'udc appena inserit0
------------------------------------------------------------------------------------------------------
SELECT * 
FROM TabStatoOdpUdc
WHERE  UDC = @UDC
------------------------------------------------------------------------------------------------------

RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[stp_DtMtrx_import]'
GO

/*****************************************
* STORED stp_DtMtrx_import
*
* Recupera dati Gitterbox/DataMatrix partendo leggendo dati successivi all'ultimo import e importanto in tab GMW
*
* Steamware, S.E.L.
* mod: 2011.04.29
*
****************************************/
ALTER PROCEDURE [dbo].[stp_DtMtrx_import]
(
	@CodCS VARCHAR(2),
	@IdxPosizione INT,
	@CodSoggetto VARCHAR(17)
)
AS

SET XACT_ABORT ON;
SET NOCOUNT ON;
/****   NON PRESENTE IN TEKAL 
------------------------------------------------------------------------------------------------------
-- leggo data ultimo import (o creo record...)
------------------------------------------------------------------------------------------------------
DECLARE		@nomeFlusso AS NVARCHAR(50)
DECLARE		@lastImport AS DATETIME
DECLARE		@trovati AS INT

-- imposto valori
SET		@nomeFlusso = 'DataMatrix'
SET		@lastImport = DATEADD(yy,-10,GETDATE()) -- inizializzo a -10 anni ...
-- sistemo tab registrazione import...
BEGIN TRAN
	-- cerco nella tab log ultima data caricamento
	SET @trovati = (
		SELECT	COUNT(*)
		FROM	logImportFlussi
		WHERE	NomeFlusso = @nomeFlusso
	)
	-- controllo se record c'...
	IF(@trovati > 0)
		BEGIN
			SET	@lastImport = ( SELECT LastImport FROM LogImportFlussi WHERE	NomeFlusso	= @nomeFlusso )
		END
	ELSE
		BEGIN
			-- lo creo!
			INSERT INTO LogImportFlussi(NomeFlusso, LastImport)
			VALUES (@nomeFlusso, @lastImport)
		END		
COMMIT TRAN
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- ora gestisco il vero caricamento...
------------------------------------------------------------------------------------------------------
	-- filtro e salvo in tabella... gitterbox validi SOLO se iniziano per "U"
	-- verifico gi se esite gi l'UDC/Gitterbox in ElencoCartellini
	DECLARE @newData TABLE
	(
		RowNumber int,
		CodDataMatrix numeric(23, 0) ,
		CodGitterbox varchar(10) ,
		NumConchiglia int ,
		NumDisegno numeric(7, 0) ,
		EsponenteDisegno tinyint ,
		CodDifettoScarto varchar(5) ,
		InizioPreparazione smalldatetime ,
		FinePreparazione smalldatetime ,
		InizioIniezione smalldatetime ,
		FineIniezione smalldatetime ,
		MagDestinazione varchar(2) ,
		DataImport smalldatetime ,
		UDC varchar(50)
	)
    INSERT INTO @newData	
    SELECT 
           ROW_NUMBER() OVER(PARTITION BY CodGitterbox ORDER BY InizioIniezione ASC),
           dm.CodDataMatrix, LTRIM(RTRIM(dm.CodGitterbox)), dm.NumConchiglia, dm.NumDisegno, dm.EsponenteDisegno,dm.CodDifettoScarto, dm.InizioPreparazione, dm.FinePreparazione, dm.InizioIniezione, dm.FineIniezione, dm.MagDestinazione, dm.DataImport,
           ec.UDC
    FROM dbo.v_trasfDataMatrix AS dm
    LEFT JOIN dbo.ElencoCartellini AS ec 
	 ON dm.CodGitterbox = ec.UDC
	WHERE DataImport > @lastImport AND LEFT(CodGitterbox, 1) = 'U'
	AND CodDifettoScarto = '00' -- non importa gli scarti!!!
		
	/* INIZIO MODIFICA  

		   Note GCarlo : 
		   
           Se il Gitterbox  gi stato trasferito e/o cmq gi presente non viene caricato, al 
           limite vengono caricati i Datamatrix non ancora presenti ma con Gitterbox = EmptyGBox

		   Se non trovo il Particolare in dbo.v_transcParticolari i datamatrix non vengono importati
		   Posso reimportarli spostando la data di import indietro in LogImportFlussi
		   
		   ATTENZIONE! Si presuppone che i DataMatrix siano coerenti ovvero stesso Particolare ecc. ecc.
    */
    BEGIN TRAN;
        
		-- creo UDC da gitterbox nuovi ( Se UDC gi presenti non li carico )
        -- GCARLO messo un RowCount prima e prendo solo il primo DataMatrix
	    INSERT INTO ElencoCartellini(UDC,CodCS, Particolare, DescParticolare, DisegnoGrezzo, Esponente, DataFus, Qta, 
	                CodSoggetto, CodStato, IdxPosizione,CreateDate, ModDate, Figura, CodImballo, Tara, PesoTot, PesoCad, NumCont, TurnoFus )
		SELECT DISTINCT  nd.CodGitterbox, @CodCS, tp.Particolare, an.DescParticolare, an.DisegnoGrezzo, an.EsponenteModifica 
		                ,InizioIniezione, dbo.getNumDatamatrix(nd.CodGitterbox), @CodSoggetto As CodSoggetto, 'Fin' AS CodStato
		                ,@IdxPosizione, GETDATE(), GETDATE()
		                ,'' AS Figura, '' AS CodImballo,0 AS Tara,0 AS PesoTot,0 AS PesoCad,1 AS NumCont,0 AS TurnoFus
		FROM @newData nd
		INNER JOIN dbo.v_transcParticolari tp           -- solo se PArticolare  presente in Trascodifica
		    ON nd.NumDisegno = tp.NumDisegno AND nd.EsponenteDisegno = tp.EsponenteDisegno
		LEFT JOIN RilPro.AnagParticolari an             -- Leggo il disegno in anagrafica
		    ON tp.Particolare = an.Particolare
		WHERE nd.RowNumber = 1                          -- Prendo solo i dati del First DataMatrix ( data iniezione )
		  AND nd.UDC IS NULL                            -- solo Gitterbox Nuovi

        
		DECLARE @udc NVARCHAR(50)
		DECLARE @Particolare NVARCHAR(50)
		DECLARE @NumDisegno NVARCHAR(50)
		DECLARE @EsponenteDisegno NVARCHAR(50)
		DECLARE @IdxCellaTo INT
		DECLARE @adesso DATETIME
		DECLARE @posTrovate INT
		
		SET		@adesso = GETDATE()
		SET		@posTrovate = 0

        -- indico cella UDC corrente... calcolo la prima cella della nuova posizione...
		SET		@IdxCellaTo = ( SELECT dbo.f_getCellaByPos(CAST(@IdxPosizione AS NVARCHAR(50))) )
		
		DECLARE cursoreImport CURSOR FOR
		SELECT DISTINCT CodGitterbox
		FROM @newData
		WHERE UDC IS NULL                            -- solo x Gitterbox Nuovi
		
		OPEN cursoreImport
		FETCH NEXT FROM cursoreImport INTO @udc

		WHILE @@FETCH_STATUS = 0
		BEGIN
		
			-- controllo se posizione non ancora creata...
			SET @posTrovate = ( SELECT COUNT(*) FROM PosizioneUdcCorrente WHERE UDC = @udc )
			IF( @posTrovate = 0)
			BEGIN
				INSERT INTO PosizioneUdcCorrente
				VALUES (@udc, @IdxCellaTo, @CodCS, @adesso)
			END
			
			FETCH NEXT FROM cursoreImport INTO @udc
		END

		CLOSE cursoreImport
		DEALLOCATE cursoreImport

	    -- CARICAMENTO DATAMATRIX -- 
		
		-- Carico i datamatrix se UDC/Gitterbox nuovi
		INSERT INTO dbo.ElencoDataMatrix
        SELECT  nw.CodDataMatrix
               ,nw.CodGitterbox
               ,nw.NumConchiglia
               ,nw.NumDisegno
               ,nw.EsponenteDisegno
               ,nw.CodDifettoScarto
               ,nw.InizioPreparazione
               ,nw.FinePreparazione
               ,nw.InizioIniezione
               ,nw.FineIniezione
               ,nw.MagDestinazione
               ,nw.DataImport
        FROM @newData AS nw
        LEFT JOIN dbo.ElencoDataMatrix AS el
          ON nw.CodDataMatrix = el.CodDataMatrix
        WHERE el.CodDataMatrix IS NULL              -- Nuovi Datamatrix 
              AND nw.UDC IS NULL                    -- Solo per Gitterbox nuovi

        -- Se esistono Datamatrix di UDC gi caricati in precedenza li inserisco ma senza 
        -- assegnare l'UDC ma lo metto a EmptyGBox perch potrebbero essere UDC gi spediti o
        -- movimentati e dovrei anche aggiornare la giacenza in ElencoCartellini
        INSERT INTO dbo.ElencoDataMatrix
        SELECT  nw.CodDataMatrix
               ,'EmptyGBox' AS CodGitterbox
               ,nw.NumConchiglia
               ,nw.NumDisegno
               ,nw.EsponenteDisegno
               ,nw.CodDifettoScarto
               ,nw.InizioPreparazione
               ,nw.FinePreparazione
               ,nw.InizioIniezione
               ,nw.FineIniezione
               ,nw.MagDestinazione
               ,nw.DataImport
        FROM @newData AS nw
        LEFT JOIN dbo.ElencoDataMatrix AS el
          ON nw.CodDataMatrix = el.CodDataMatrix
        WHERE el.CodDataMatrix IS NULL
          AND nw.UDC IS NOT NULL

	    -- aggiorno dati ultimo caricamento
	    SET		@lastImport = ( SELECT ISNULL(MAX(DataImport),@lastImport) FROM @newData )
	    UPDATE	LogImportFlussi
	    SET		LastImport = @lastImport
	    WHERE	NomeFlusso = @nomeFlusso
   COMMIT TRAN;
*/
RETURN
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering trigger [dbo].[trg_storicoUDC] on [dbo].[ElencoCartellini]'
GO

-- =============================================
-- Author:		Samuele E. Locatelli
-- Create date: 2009-10-27
-- Description:	trigger x inserimento valori in archivio storico alla cancellazione/modifica del dato
-- =============================================
ALTER TRIGGER [dbo].[trg_storicoUDC] 
   ON  [dbo].[ElencoCartellini] 
   AFTER DELETE,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- metto in storico la posizione attuale dell'UDC (SE esiste) CANCELLATO
	INSERT INTO StoricoCartellini(DataEv, UDC, CodCS, CodCliente, RagSociale, Particolare, DescParticolare, DisegnoGrezzo, Esponente, CodImpianto,
				DescImpianto, CodStampo, Figura, DataFus, TurnoFus, CodImballo, CodSoggetto, NumCont, Tara, Qta, CodStato, IdxPosizione, PesoTot, PesoCad, Note)
	(
		
		SELECT		GETDATE(), UDC, CodCS, CodCliente, RagSociale, Particolare, DescParticolare, DisegnoGrezzo, Esponente, CodImpianto, DescImpianto, 
					CodStampo, Figura, DataFus, TurnoFus, CodImballo, CodSoggetto, NumCont, Tara, Qta, CodStato, IdxPosizione, PesoTot, PesoCad, Note
		FROM	deleted
	)

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering extended properties'
GO
EXEC sp_updateextendedproperty N'Note', N'Tabella di decodifica il CodRaggruppamento Magazzino per Stampa Odette', 'SCHEMA', N'RilPro', 'TABLE', N'TabDecodBolla', NULL, NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_updateextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "AnagStatiProdotto"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 101
               Right = 208
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ElencoCartellini"
            Begin Extent = 
               Top = 6
               Left = 246
               Bottom = 309
               Right = 416
            End
            DisplayFlags = 280
            TopColumn = 12
         End
         Begin Table = "Celle"
            Begin Extent = 
               Top = 27
               Left = 818
               Bottom = 156
               Right = 988
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PosizioneUdcCorrente"
            Begin Extent = 
               Top = 71
               Left = 548
               Bottom = 200
               Right = 718
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Blocchi"
            Begin Extent = 
               Top = 70
               Left = 1080
               Bottom = 199
               Right = 1250
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagMag"
            Begin Extent = 
               Top = 188
               Left = 799
               Bottom = 334
               Right = 969
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 22
         Width = 284
         Width = 15', 'SCHEMA', N'dbo', 'VIEW', N'v_UdcDetail', NULL, NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating extended properties'
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "ec"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 336
               Right = 208
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "rq"
            Begin Extent = 
               Top = 15
               Left = 364
               Bottom = 298
               Right = 534
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 40
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1', 'SCHEMA', N'dbo', 'VIEW', N'v_RapQualNote', NULL, NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_addextendedproperty N'MS_DiagramPane2', N'170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'SCHEMA', N'dbo', 'VIEW', N'v_RapQualNote', NULL, NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', 2, 'SCHEMA', N'dbo', 'VIEW', N'v_RapQualNote', NULL, NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO